CODEWITHSUNDEEP
sqlms-access
DanM
DanM

Reputation: 7

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?

Upvotes: 0

Views: 7476

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

I think the right way to do this in Access is:

UPDATE table
    SET field = LEFT(field, LEN(field) - 1)
    WHERE field LIKE '*.' ;

Note that the like wildcards are different in MS Access.

Upvotes: 2

Rion Williams
Rion Williams

Reputation: 76557

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

Upvotes: 1

Related Questions