Combining 2 Text Feilds

[Tim]

I receive information in a text file that contains 2 address fields. Some
times the 2nd address field is blank and sometimes it contains apt numbers
or in care information. I need to combine the fields into 1 address filed
using a query. When I do this the records with data in the 2nd address
return the contents of the 2 fields but the records with nothing in the 2nd
address field return nothing. Any help is greatly appreciated

Tim

 

[Allen Browne]

Try a text box with Control Source of:
=Trim([Address1] & " " & [Address2])

If you want them on two lines:
=Trim([Address1] & Chr(13) + Chr(10) + [Address2])

This relies on a subtle difference between the normal concatenation operator
(&) and "+":
"Fred" & Null = "Fred"
"Fred" + Null = Null

 

[Tim]

That works. Thanks for the help.

Tim

Try a text box with Control Source of:
=Trim([Address1] & " " & [Address2])

If you want them on two lines:
=Trim([Address1] & Chr(13) + Chr(10) + [Address2])

This relies on a subtle difference between the normal concatenation operator
(&) and "+":
"Fred" & Null = "Fred"
"Fred" + Null = Null
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I receive information in a text file that contains 2 address fields. Some
times the 2nd address field is blank and sometimes it contains apt numbers
or in care information. I need to combine the fields into 1 address filed
using a query. When I do this the records with data in the 2nd address
return the contents of the 2 fields but the records with nothing in the 2nd
address field return nothing. Any help is greatly appreciated