Trim Data Question

[sibleytr]

I am needing to build a field using the data from four other fields using an Access Query.

strFullStName: [PREDIRECT]&" "&[STREETNAME]&" "&[STREETTYPE]&" "&[POSTDIRECT]

From the above code you can see the problem if any field, specificly the outer two fields are empty I wind up with a space in from of and/or behind my created data if some data fields are null.

Any ideas on how I can set an IIf condition or simply trim out the spaces on either end without making a second pass over the data?

 

[traingamer]

To just trim the outlying spaces:

strFullStName: Trim([PREDIRECT]& " " &[STREETNAME]& " " & [STREETTYPE]& " " & [POSTDIRECT])

This, of course, does nothing with the internal spaces...

traingamer

 

[PHV]

strFullStName: [PREDIRECT] & IIf(IsNull([STREETNAME]),""," " & [STREETNAME]) & IIf(IsNull([STREETTYPE]),""," " & [STREETTYPE]) & IIf(IsNull([POSTDIRECT]),""," " & [POSTDIRECT])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sibleytr]

I don't know what my computer has been smoking but I re-ran my previous.

FullStreetName: Trim([PREDIRECT] & " " & [STREETNAME] & " " & [STREETTYPE] & " " & [POSTDIRECT])

and the query worked this time.

Time to crush the bugs with a format do over.

Thanks again.

 

[PHV]

and the query worked this time
Even with a null STREETTYPE and a valid POSTDIRECT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sibleytr]

the Trim command by itself only trimed the outer ends of the data, I had to use your IIF to make it really massage the data they proper way.

Thanks for the code. It is greatly apprecaited.