Remove spaces when concetenating?

[mkallover]

I'm creating a simple Select query as a source for a Report and in the query I am concatenating several fields like so:

(PRESBR_LAST_NME & ", " & PRESBR_FRST_NME & " " & PRESBR_MDL_NME & " " & PRESBR_SUFX) AS FullName

The problem is that the data in each field is space filled in order to use all characters. Is there a way to remove the spaces when concatenating so the results don't look like:

Smith           , John        A        Jr.

 

[Golom]

Use Trim$ on each of your field names.

 

[mkallover]

Ahhhh, apparently my Google-Fu was weak this morning when I started looking for a solution. I just found TRIM, RTRIM and LTRIM. So for posterity's sake I replaced my original concatenation with:

(RTRIM(PRESBR_LAST_NME) & ", " & RTRIM(PRESBR_FRST_NME) & " " & RTRIM(PRESBR_MDL_NME) & " " & RTRIM(PRESBR_SUFX)) AS FullName
[/code}

 

[genomon]

You can also use REPLACE().

Beir bua agus beannacht!

 

[danvlas]

Especially when the space is found in the middle of the string


Daniel Vlas
Systems Consultant