DanM DanM - 1 year ago 66
SQL Question

Removing last character in ACCESS if it is "."

I am trying to write an update query that will remove the last character if it is a period ("."). In EXCEL, I would use the following statement:


How can I modify this for ACCESS? I found the following statement in a forum, but ACCESS seems to have a problem with "Substring" and won't let me run the query.

UPDATE table SET field = SUBSTRING(field, 1, CHAR_LENGTH(field) - 1)WHERE field LIKE '%.'

Any thoughts?

Answer Source

You could simply create a substring that was one character shorter than your existing string if it ended with a period via the LEFT() function :

UPDATE YourTable
   SET YourColumn = LEFT(YourColumn, LEN(YourColumn - 1))
 WHERE YourColumn LIKE '*.'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download