stripping characters from text field

[bird lover]

Working in access 2003

In text field [local], a number of records, but not all have four zeros at
the end which I want to strip off. Examples are 123450000 or 34567 or field
is just blank.
What is syntax to put into query to strip off zeros.
Thanks for any help.

 

[Allen Browne]

Use an expression like this in an Update query:
IIf([local] Is Null, Null, Left([local], Len([local])-4))

Note that Local is a reserved word, so not a good field name. Here's a list
to refer to when designing tables:
http://allenbrowne.com/AppIssueBadWord.html#L

 

[John W. Vinson]

Working in access 2003

In text field [local], a number of records, but not all have four zeros at
the end which I want to strip off. Examples are 123450000 or 34567 or field
is just blank.
What is syntax to put into query to strip off zeros.
Thanks for any help.

UPDATE yourtable
SET [local] = Left(Len([Local]) - 4)
WHERE [local] LIKE "*0000")

will do so. Back up your database first. Of course you might have a value like
10000 that really IS 10000, not 1, and you would destroy data; you might want
some other criteria on the length of the field!

My suggestion assumes that the field is Text. If it's numeric, then

UPDATE yourtable
SET [local] = [local] / 10000
WHERE [local] MOD 10000 = 0