Removing Trailing chr(160) Space in Query

[Elysynn]

Hello - just wanted to share something I figured out today after trying to find a solution that didn't involve VB.

If you need to remove a trailing space that TRIM does not remove, the following expression could help (Access 2003):

IIf(Right([YourField],1)=Chr(160),Replace([YourField],Chr(160),""),[YourField])

Full SQL example

SELECT first_name, last_name, user_id, usr_login, IIf(Right([usr_login],1)=Chr(160),Replace([usr_login],Chr(160),""),[usr_login]) AS CleanUsr_login
FROM tblUsers;

I haven't tried it, but the same should apply to leading spaces as well.

To the gurus out there, if there is any reason why this isn't a good idea or speed bumps one may encounter while using this expression - please share!

-Elysynn

 

[PHV]

Chr(160) is the NonBreakSpace character, I think.
I'd use this:
Trim(Replace([yourField] & "", Chr(160), " "))

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

 

[Elysynn]

Thanks, PHV for an even simpler solution!

-Elysynn