Question Locked

How can I truncate data in a MS Access Field?

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Using Google, I found the following update query posted by Tony Hopkinson in Mar 2005:

update mytable set ssn = right$(ssn,4)

I tried this and it works for all MS Access databases that I have except for one. In that database, I get the following error when I run the query:

Compile error. in query xpression 'right$(ssn,4)

Any suggestions would be greatly appreciated.

Semper Fidelis,

Joe Grabman

3 replies

by tony hopkinson

right$(ssn,4) will take the last four characters.

left$(ssn,4) would truncate.
mid(ssn,1,4) would
substring(ssn,1,4) is the way it would be done in SQL as opposed to VBA.

As for why you get the error in this one particular database.

Does ssn exist, is it a string type, is it a different version of access?
...

Mar 2005....

This discussion is closed to new replies.