Trim Joined texts 1

[withoutwax]

I would be grafeful for any assistance. I have created a text field and in it I have joined text from other fields:
=([title] & " " & [first name] & " " & [surname] & "
" & [position] & "
" & [address1] & "
" & [address2] & "
" & [address3] & "
" & [address4] & "
" & [countyID] & "
" & [postcode])

What I would like to do is trim each field so that if there is no data in say "address3" then I would like what's below to move up and fill the gap. Is this possible?

 

[pjm]

If you concatentate using "+" then null values propagate.
So:

[title] & " " & [first name] & " " & [surname] [red]&[/red] [position] [red]+[/red] chr(13) [red]+[/red] chr(10) [red]&[/red] [address1] [red]+[/red] chr(13) [red]+[/red] chr(10) [red]&[/red] [address2] [red]+[/red] chr(13) [red]+[/red] chr(10)...

HTH
pjm

 

[jsteph]

withoutwax,
I make it a habit of wrapping everything that's going to be put in a string field or variable in Trim():
SomeFieldOrVariable = Trim("" & Some_Other_FieldOrVariable)

Since Trim() alone could return a null if the arg is a variant or null db field, I just got in the habit of doing a concat inside trim with a blank string--it will always return a string even if the arg is null. There are probably other ways to skin that cat, but that's mine.

Also, since Trim$() only accepts a string, and I don't like wondering about using the $ functions and what argument they may or may not hold, so just use Trim().

If you're doing slightly more complex stuff, like needing logic to use a comma after, say, a last name but don't want the comma if the following first name is blank, then you'll need to look at the IIF() function.
--Jim