DanM DanM - 4 months ago 19x
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?


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 '*.'