Mailing Address

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?

On Fri, 6 Feb 2009 20:42:00 -0800, Roger Bell
wrote:

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?

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.
--

John W. Vinson [MVP]

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" wrote:

On Fri, 6 Feb 2009 20:42:00 -0800, Roger Bell
wrote:

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?

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.
--

John W. Vinson [MVP]

On Sat, 7 Feb 2009 02:19:02 -0800, Roger Bell
wrote:

Thanks for you help, John.

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

Please help if you are able

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].

--

John W. Vinson [MVP]

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" wrote:

On Sat, 7 Feb 2009 02:19:02 -0800, Roger Bell
wrote:

Thanks for you help, John.

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

Please help if you are able

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].

--

John W. Vinson [MVP]

On Sun, 8 Feb 2009 18:03:01 -0800, Roger Bell
wrote:

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]

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

John W. Vinson [MVP]