Concatenating Multiple Fields Containing Spaces 1

[rosieb]

Hi

I need to concatenate 9 fields in a query with a space between each item. However for any row, most of the fields contain a single space. I want to avoid excess spaces.

I know I could do this with a lot of IIFs, but that seems untidy. & vs + won't help because of the embedded spaces, and I can't change the data.

Can anyone point me towards a better approach to this.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[r937]

select trim(column1 & ' ')
     & trim(column2 & ' ')
     & trim(column3 & ' ')
     & trim(column4 & ' ')
     & trim(column5 & ' ')
     & trim(column6 & ' ')
     & trim(column7 & ' ')
     & trim(column8 & ' ')
     & trim(column9) as concat
  from daTable

r937.com | rudy.ca

 

[rosieb]

Hi r937

Thanks for that,I now have:

Address1: Trim([Organisation] & ' ') & Trim([Number_] & ' ') & Trim([SubName] & ' ') & Trim([Name] & ' ') & Trim([Thoroughfare] & ' ') & Trim([DepThoroughfare] & ' ') & Trim([DepLocality] & ' ') & Trim([DoubLocality] & ' ') & Trim([Posttown])

which gets me part way there, but I now have no spaces between the fields.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[r937]

oh. yeah.. sorry.

select trim(
       trim(column1) & ' '
     & trim(column2) & ' '
     & trim(column3) & ' '
     & trim(column4) & ' '
     & trim(column5) & ' '
     & trim(column6) & ' '
     & trim(column7) & ' '
     & trim(column8) & ' '
     & trim(column9)
       ) as concat
  from daTable

r937.com | rudy.ca

 

[rosieb]

No, still not quite - that gives an extra space if the field is blank.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[r937]

if the field is blank? which field?

r937.com | rudy.ca

 

[PHV]

As you don't want to follow the IIf way, create the following function in a standard code module:

Public Function mySpacesKiller(lst)
If IsNull(lst) Then Exit Function
lst = Trim(lst)
While InStr(lst, "  "): lst = Replace(lst, "  ", " "): Wend
mySpacesKiller = lst
End Function

And now in the query grid:
Address1: mySpacesKiller([Organisation] & ' ' & [Number_] & ' ' & [SubName] & ' ' & [Name] & ' ' & [Thoroughfare] & ' ' & [DepThoroughfare] & ' ' & [DepLocality] & ' ' & [DoubLocality] & ' ' & [Posttown])

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

 

[rosieb]

PHV - that's great, works a treat! Thanks a lot. I'm working with a lot of different address data sets with similar issues - I'm sick of IIFs, this is going to be REALLY useful.


r937 - a lot of the fields only contain a single space. It's fiddly data. Thanks for your help.

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)