将多个地址字段组合成1个地址字段 [英] combining multiple address fields into 1 address field
问题描述
我在表格中有以下地址字段：
flat_number
house_name_or_number
street
村
postal_town
县
邮政编码
country
因为我不能强制要求所有字段，所以我想创建一种方法来获取
仅捕获具有数据的字段中的数据把它放在另一个名为''address''的
字段中，以便该字段按常规读取（即每个字段
在一个单独的行上 - 如果字段要向上移动，如果没有数据在
之前的字段中。）
我曾想过使用IIf语句进行更新查询但是我看不到
用于回车的字符。
任何帮助都会非常感激。
问候，
Hal。
11月27日上午7:24， H" < h ... @ oldspacewestern.co.ukwrote：
我有以下地址字段一张桌子：
flat_number
house_name_or_number
street
village
postal_town
县
邮政编码
国家
由于我不能强制要求所有字段，我'我想创建一种方法来获取仅包含数据的字段中的数据并将其放入另一个名为地址的另一个字段中，以便该字段按常规读取（即每个字段
在一个单独的行上 - 如果
前面字段中没有数据，则行向上移动。）
我曾想过使用IIf语句进行更新查询，但我看不到
用于''回车'的字符。
任何帮助都将不胜感激。
问候，
哈尔。
用查询查看数据可能会更好，而不是添加另一个字段。
添加另一个字段。
无论哪种方式，您可能都在寻找一种方法将
字段连接在一起。它是这样的东西
选择flat_number& " " &安培; house_name_or_number& " " &安培;街道。 。 。
注意字段之间增加的空格，''& " " &''，这使得
单词以空格分隔。但是，如果你缺少一两个字段，那么你将在字符串中获得双倍空格。最简单的方法是用双倍空格构建字符串，然后用替换函数清理
之后：
选择替换（flat_number&""& house_name_or_number&""&
street，""，""）
" Ben" < bm ****** @ gmail.com写信息
新闻：3b ************************ ********** @ s19g2000 prg.googlegroups.com ...
11月27日上午7:24，H < h ... @ oldspacewestern.co.ukwrote：
我有以下地址字段一张桌子：
flat_number
house_name_or_number
street
village
postal_town
县
邮政编码
国家
由于我不能强制要求所有字段，我' '我想创建一种方式来获取
仅捕获包含数据的字段中的数据并将其放入
另一个
字段名为''address''，以便该字段按常规读取（即每个
字段
在一个单独的行上 - 如果没有数据则行向上移动在
前面的字段。）
我曾想过使用IIf语句进行更新查询，但我可以'' t
参见
使用什么字符''回车''。
我们非常感谢任何帮助。
问候，
哈尔。
用查询查看数据可能会更好，而不是添加另一个字段。
添加另一个字段。
无论哪种方式，您可能都在寻找一种方法将
字段连接在一起。它是这样的东西
选择flat_number& " " &安培; house_name_or_number& " " &安培;街道。 。 。
注意字段之间增加的空格，''& " " &''，这使得
单词以空格分隔。但是，如果你缺少一两个字段，那么你将在字符串中获得双倍空格。最简单的方法是用双倍空格构建字符串，然后用替换函数清理
之后：
选择替换（flat_number&""& house_name_or_number&""&
street，""，""）
战略性地替换&使用+将消除
字段为空的额外空间。
string + NULL返回NULL
string& NULL返回字符串
选择flat_number& （" + house_name_or_number）& （" + + street ...）
" Ben" < bm ****** @ gmail.com写信息
新闻：3b ************************ ********** @ s19g2000 prg.googlegroups.com ...
11月27日上午7:24，H < h ... @ oldspacewestern.co.ukwrote：
>
我在表格中有以下地址字段：
flat_number
house_name_or_number
街道
村庄
postal_town
县
邮政编码
国家
自从我不能强制要求所有字段，我想创建一种方法来仅捕获包含数据的字段中的数据并将其放入另一个名为''的字段中。地址''使得字段按常规读取（即每个字段
在单独的行上 - 如果前面的字段中没有数据，则行向上移动。）
我曾想过使用IIf语句进行更新查询，但我不能看到
用于回车的字符。
非常感谢任何帮助。
问候，
哈尔。
用查询查看数据可能会更好，而不是添加另一个字段。
添加另一个字段。
无论哪种方式，您可能都在寻找一种方法将
字段连接在一起。它是这样的东西
选择flat_number& " " &安培; house_name_or_number& " " &安培;街道。 。 。
注意字段之间增加的空格，''& " " &''，这使得
单词以空格分隔。但是，如果你缺少一两个字段，那么你将在字符串中获得双倍空格。最简单的方法是用双倍空格构建字符串，然后用替换函数清理
之后：
选择替换（flat_number&""& house_name_or_number&""&
street，""，""）
感谢您的输入。
我已经创建了这样的查询，但我真的希望地址显示为
在一个领域内的方式与通常的方式相同
写的;即如果使用了所有字段，那么地址就会出现：
平1，
智能房子
Cred Street
小村庄
大镇
Countyshire
AB1 2CD
苏格兰
或者，如果只有少数几个字段包含数据，它就会显示出来......
Smart House
Cred Street
大城市
AB1 2CD
苏格兰
这将为地址/电话创建报告书籍或邮件合并
更容易，因为我只需要调用1个字段，而不是每次都写入
表达式。
我已经创建了由
逗号和空格分隔的单行合并文本 - 但是我看不到如何插入回车符？
问候Hal
Hal
Hi,
I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country
Since I can''t make all fields mandatory, I''d like to create a way of
capturing only the data in fields that have data and putting that in another
field called ''address'' so that field reads conventionally (i.e each fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)
I had thought of making an update query using IIf statements but I can''t see
what character to use for ''carriage return''.
Any help would be much appreciated.
Regards,
Hal.
On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:Hi,
I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country
Since I can''t make all fields mandatory, I''d like to create a way of
capturing only the data in fields that have data and putting that in another
field called ''address'' so that field reads conventionally (i.e each fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)
I had thought of making an update query using IIf statements but I can''t see
what character to use for ''carriage return''.
Any help would be much appreciated.
Regards,
Hal.It may be better to do this with a query to view the data, rather then
adding another field.
Either way you are probably looking for a way to concatenate the
fields together. It goes something like this
Select flat_number & " " & house_name_or_number & " " & street . . .
Notice the added spaces between the fields, ''& " " &'', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:
Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")
"Ben" <bm******@gmail.comwrote in message
news:3b**********************************@s19g2000 prg.googlegroups.com...On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:Hi,
I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country
Since I can''t make all fields mandatory, I''d like to create a way of
capturing only the data in fields that have data and putting that in
another
field called ''address'' so that field reads conventionally (i.e each
fields
is on a separate line - and for lines to be moved up if no data is in
the
preceding field.)
I had thought of making an update query using IIf statements but I can''t
see
what character to use for ''carriage return''.
Any help would be much appreciated.
Regards,
Hal.
It may be better to do this with a query to view the data, rather then
adding another field.
Either way you are probably looking for a way to concatenate the
fields together. It goes something like this
Select flat_number & " " & house_name_or_number & " " & street . . .
Notice the added spaces between the fields, ''& " " &'', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:
Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")Strategically replacing & with + will eliminate the extra space where the
field is null.
string + NULL returns NULL
string & NULL returns string
Select flat_number & (" " + house_name_or_number) & (" " + street . . .)
"Ben" <bm******@gmail.comwrote in message
news:3b**********************************@s19g2000 prg.googlegroups.com...On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:>Hi,
I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country
Since I can''t make all fields mandatory, I''d like to create a way of
capturing only the data in fields that have data and putting that in
another
field called ''address'' so that field reads conventionally (i.e each
fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)
I had thought of making an update query using IIf statements but I can''t
see
what character to use for ''carriage return''.
Any help would be much appreciated.
Regards,
Hal.
It may be better to do this with a query to view the data, rather then
adding another field.
Either way you are probably looking for a way to concatenate the
fields together. It goes something like this
Select flat_number & " " & house_name_or_number & " " & street . . .
Notice the added spaces between the fields, ''& " " &'', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:
Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")
Thanks for the input.
I have already created such a query but I really wanted the addresses to be
displayed within a field in just the same way that they would normally be
written; i.e. if all the fields are used an address would appear thus:
Flat 1,
The Smart House
Cred Street
Little Village
Big Town
Countyshire
AB1 2CD
Scotland
Or, if only a few fields contain data it would be displayed so...
Smart House
Cred Street
Big Town
AB1 2CD
Scotland
This would make creating reports for address / phone books or mail merges
much easier as I would only ever had to call 1 field, instead of writing the
expression each time.
I''ve got as far as creating a a single line of merged text separated by a
comma and space - but I can''t see how to insert a carriage return?
Regards Hal
Hal
这篇关于将多个地址字段组合成1个地址字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
