Populating textbox with more than one row of text

[Wullie]

Hi,

I'm starting to go round in circles now and it's starting to do my head in -
I'm sure I'm missing something simple.

I have a table which contains 4 rows of an address separately (5 including
postcode) and want to display the address in one text box.

When I put the below into my text box, it shift's the row down regardless of
whether the field is blank or not

=IIf([Address1]="","",([Address1] & Chr(13) & Chr(10))) &
IIf([address2]="","",([Address2] & Chr(13) & Chr(10))) &
IIf([address3]="","",([Address3] & Chr(13) & Chr(10))) &
IIf([address4]="","",([Address4] & Chr(13) & Chr(10))) &
IIf([postcode]="","",[postcode])

At the moment I am getting

<Blank>
Address2
<Blank>
Address4
Postcode

rather than the output of

Address2
Address4
Postcode

that I would expect to get.

Anyone have any ideas what I've done wrong?

Thanks

 

[Allen Browne]

You are testing for a zero-length string, which is not the same as Null. For
more about that, see error #5 in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

Anyway, this may be easier:
=[Address1] + Chr(13) + Chr(10)
& [Address2] + Chr(13) + Chr(10)
& [Address3] + Chr(13) + Chr(10)
& [Address4] + Chr(13) + Chr(10)
& [postcode]

This expression relies on a subtle difference between the 2 concatenation
operators in Access:
"A" & Null => "A"
"A" + Null => Null

 

[Linq Adams via AccessMonster.com]

You're checking to see if if the fields have a zero-length string:

[Address1]=""

Since a blank field usually is NULL rather than holding a zero-length string,
your code prints the NULL field then moves to the next line.

You need to check the fields for NULL! This

= IIf(IsNull([Address1]), "", ([Address1] & Chr(13) & Chr(10))) & _
IIf(IsNull([Address2]), "", ([Address2] & Chr(13) & Chr(10))) & _
IIf(IsNull([Address3]), "", ([Address3] & Chr(13) & Chr(10))) & _
IIf(IsNull([Address4]), "", ([Address4] & Chr(13) & Chr(10))) & _
IIf(IsNull([PostCode]), "", [PostCode])

does the job for me, producing

Address2
Address4
Postcode