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:

=IF(RIGHT(A1,1)=".",LEFT(A1,LEN(A1)-1),A1)

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

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