Mailing Address

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

I have the following text box on a form:

=Trim([titles] & " " & [firstname]) & " " & [lastname] & Chr(13) & Chr(10) &
Trim([unit/flat] & " " & [unit/flat no] & Chr(13) & Chr(10) & "" & ([street
number] & "" & [street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] & " " & [state] & " " & [pcode]))

What I would like is when there is no Unit/Flat, then this line space will
be closed up. Could someone kindly advise how this can be achieved?
 
I have the following text box on a form:

=Trim([titles] & " " & [firstname]) & " " & [lastname] & Chr(13) & Chr(10) &
Trim([unit/flat] & " " & [unit/flat no] & Chr(13) & Chr(10) & "" & ([street
number] & "" & [street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] & " " & [state] & " " & [pcode]))

What I would like is when there is no Unit/Flat, then this line space will
be closed up. Could someone kindly advise how this can be achieved?
Click to expand...

There's a sneaky way to do it, using the fact that both the & and + operators
concatenate strings. They do it differently: & treats a NULL value as an empty
string - e.g. [FieldA] & [FieldB] returns the value in fieldA if fieldB is
null; + propagates nulls, so that [FieldA] + [FieldB] is NULL if either one is
null.

Secondly, you don't need TRIM unless you have leading blanks... and the same
trick can get rid of them.

Try

([titles] + " ") & [firstname] & " " & [lastname] & Chr(13) & Chr(10) &
([unit/flat] + " " + [unit/flat no] + Chr(13) & Chr(10)) & ([street
number] & ([street prefix] + " ") & [street name] & Chr(13) & Chr(10) &
([suburb] + " ") & [state] & " " & [pcode]

Just for example, if [Titles] contains "Mr." the first parenthetical
expression will resolve to "Mr. " (appending the blank); if there is nothing
in Titles, the expression will be NULL and the first line will start with the
first name.
 
Thanks for you help, John.

Tried that, but get an error saying "Extra) in query expression"

Please help if you are able

John W. Vinson said:
I have the following text box on a form:

=Trim([titles] & " " & [firstname]) & " " & [lastname] & Chr(13) & Chr(10) &
Trim([unit/flat] & " " & [unit/flat no] & Chr(13) & Chr(10) & "" & ([street
number] & "" & [street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] & " " & [state] & " " & [pcode]))

What I would like is when there is no Unit/Flat, then this line space will
be closed up. Could someone kindly advise how this can be achieved?
Click to expand...

There's a sneaky way to do it, using the fact that both the & and + operators
concatenate strings. They do it differently: & treats a NULL value as an empty
string - e.g. [FieldA] & [FieldB] returns the value in fieldA if fieldB is
null; + propagates nulls, so that [FieldA] + [FieldB] is NULL if either one is
null.

Secondly, you don't need TRIM unless you have leading blanks... and the same
trick can get rid of them.

Try

([titles] + " ") & [firstname] & " " & [lastname] & Chr(13) & Chr(10) &
([unit/flat] + " " + [unit/flat no] + Chr(13) & Chr(10)) & ([street
number] & ([street prefix] + " ") & [street name] & Chr(13) & Chr(10) &
([suburb] + " ") & [state] & " " & [pcode]

Just for example, if [Titles] contains "Mr." the first parenthetical
expression will resolve to "Mr. " (appending the blank); if there is nothing
in Titles, the expression will be NULL and the first line will start with the
first name.
Click to expand...
 
Thanks for you help, John.

Tried that, but get an error saying "Extra) in query expression"

Please help if you are able
Click to expand...

Sorry about the typo. Did you try counting parentheses yourself to see where I
made a mistake? The trick I use is to count left to right adding 1 for each
left paren and subtracting 1 for each right: let's try it...

([titles] + " ") & [firstname] & " " & [lastname] & Chr(13) & Chr(10) &
([unit/flat] + " " + [unit/flat no] + Chr(13) & Chr(10)) & [street
number] & ([street prefix] + " ") & [street name] & Chr(13) & Chr(10) &
([suburb] + " ") & [state] & " " & [pcode]


ok, there was an extra ( in front of [Street Number].
 
Thanks again John,

Have altered as follows, but still receiving the Extra) error message. Have
counted for 4( & 4) as kindly suggested

([titles]+" ") & [Firstname] & " " & [Lastname] & Chr(13) & Chr(10) &
([unit/flat]+" "+[unit/flat no]+Chr(13) & Chr(10)) & [street number] &
([street prefix]+" ") & [street name] & Chr(13) & Chr(10) & ([suburb]+" ") &
[state] & " " & [pcode]

Any thoughts



John W. Vinson said:
Thanks for you help, John.

Tried that, but get an error saying "Extra) in query expression"

Please help if you are able
Click to expand...

Sorry about the typo. Did you try counting parentheses yourself to see where I
made a mistake? The trick I use is to count left to right adding 1 for each
left paren and subtracting 1 for each right: let's try it...

([titles] + " ") & [firstname] & " " & [lastname] & Chr(13) & Chr(10) &
([unit/flat] + " " + [unit/flat no] + Chr(13) & Chr(10)) & [street
number] & ([street prefix] + " ") & [street name] & Chr(13) & Chr(10) &
([suburb] + " ") & [state] & " " & [pcode]


ok, there was an extra ( in front of [Street Number].
Click to expand...
 
Thanks again John,

Have altered as follows, but still receiving the Extra) error message. Have
counted for 4( & 4) as kindly suggested

([titles]+" ") & [Firstname] & " " & [Lastname] & Chr(13) & Chr(10) &
([unit/flat]+" "+[unit/flat no]+Chr(13) & Chr(10)) & [street number] &
([street prefix]+" ") & [street name] & Chr(13) & Chr(10) & ([suburb]+" ") &
[state] & " " & [pcode]
Click to expand...

Count the entire query. I can't see it from here.
 
Back
Top