We help IT Professionals succeed at work.

Access 2007 --  Macro to trim strings and delete end of field commas

tomfolinsbee
tomfolinsbee asked
on
Hello Experts

I have a query called qryRemoveCommas that displays address fields.

Many of the address fields have commas at the  end. Many of the address fields also have spaces at the end of the string.

What the most efficient way to ensure that I have removed all the commas at the end of the string -- but leave any commas inside the string intact.

Thanks!

Comment
Watch Question

Top Expert 2016

Commented:
this will not be easy.. by commas are the number of commas consistent or not.

if not, you will need a function to clean the the address field

Author

Commented:
Yes, would like a function to do this.

"14 Bridge Street, Ottawa," would return "14 Bridge Street, Ottawa"

Top Expert 2016

Commented:
if only one comma, you can do this

iif(right([address],1)=",",left([address],len([address])-1),[address])
Top Expert 2016
Commented:
update tablex
set [address]=iif(right([address],1)=",",left([address],len([address])-1),[address])

Author

Commented:
thx