If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Mailing Address
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] |
#3
|
|||
|
|||
Mailing Address
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] |
#4
|
|||
|
|||
Mailing Address
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] |
#5
|
|||
|
|||
Mailing Address
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] |
#6
|
|||
|
|||
Mailing Address
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] |
Thread Tools | |
Display Modes | |
|
|