Field entry Problems

[bilbo+]

Hi there, i have an address memo box that is used to show the entries of each
line of address that are entered, they are entered by first line, county etc
and then I have created this...
=[address] & "," & (Chr(13)) & (Chr(10)) & [city] & "," & (Chr(13)) &
(Chr(10)) & [County] & "," & (Chr(13)) & (Chr(10)) & [PostalCode] & "," &
(Chr(13)) & (Chr(10)) & [country] & "," & (Chr(13)) & (Chr(10))

to show them all in the memo box as one, However i was wondering would there
be a way of ignoring the comma every time there isnt an entry inthe previous
field, for example, if there wasnt a county entry could i get it to skip the
comma that should go after it?
Thanks for your help, WK

 

[Damian S]

Hi bilbo+,

To ignore a comma, use iif like this:

iif(isnull([County]), "", [County] & ",")

where you currently have [County] & ","

The same for other fields you want to test for null.

Hope this helps.

Damian.

 

[Douglas J. Steele]

Take advantage of the fact that + and & work differently when used to
concatenate strings: if you use + to concatenate to a Null, the result will
be Null (wherease if you use & to concatenate to a Null, the result will be
whatever you're trying to concatenate to the Null)

=([address] + "," & Chr(13) & Chr(10)) & ([city] + "," & Chr(13) & (Chr(10))
& ([County] + "," & Chr(13) & Chr(10)) + ([PostalCode] + "," & Chr(13) &
(Chr(10)) & ([country] + "," & Chr(13) & Chr(10))

I believe the parentheses are required.