stripping characters from text field

  • Thread starter Thread starter bird lover
  • Start date Start date
B

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.
 
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.
Click to expand...

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
 
Back
Top