Concatanate Address Fields

[Aurora]

I am using access 2000.

In my database we have 8 fields for the address (CoName,
AttnName,Add1,Add2,City,St,Zip,Country). I know how to
set up the City,Statea and Zip to run together on one
line. But how do I put the first part of the address
(CoName, AttnName, Add1 and Add2) together and not leave a
blank line, if one of the fields are null.

Please help, Aurora

 

[Allen Browne]

The simplest solution is to use 4 text boxes, with their Control Source of:
Add1
Add2
=Trim([City] & " " & [St] & " " & [Zip])
Country
Set the CanShink property of the boxes to Yes.
If the box is blank, it will shrink so the others close up underneath it.

Another alternative is to use the trick that:
"Something" & Null => "Something"
whereas:
"Something" + Null => Null
Therefore you can use a single text box with Control Source of:
=[Add1] & Chr(13) & Chr(10) + [Add2] & Chr(13) & Chr(10) + Trim([City] & " "
& [St] & " " & [Zip]) & Chr(13) & Chr(10) + [Country]

 

[Brian Smith]

Try setting the can shrink property for each of these values to Yes.