How to Add & Delete a Digit Using a Query

[bethabernathy]

Hi - I have a table named "TESTNEW" there is a field in this table named "CustomerID". The data in the "CustomerID" contains 6 numeric digits i.e. "000049" or "000205". I need to run a query that will delete the last digit from the data. I also need to be able to run another query that will add a "0" to the beginning of the data. So, ultimately the above numbers would change to "000004" and "000020". Does anyone know how to do this? Thanks, Beth beth@integratedresourcemgmt.com

 

[swampBoogie]

To remove the last digit

update testnew set CustomerID = left(CustomerID,5)
where ...

to add a leading zero

update testnew set CustomerID = '0' & CustomerID
where ...

or as one query

update testnew set CustomerID = '0' & left(CustomerID,5)
where ...

 

[bethabernathy]

Hi Thanks - That is working, but what if I now need to change the first two digits to an "01"? How would that work their is criteria in the query so this is what I have so far:

UPDATE testnew SET testnew.CustomerID = Left("CustomerID",5)
WHERE (((testnew.CustomerID) Like "00*" And (testnew.CustomerID) Not Like "000*&quot);

So rather than adding a "0" to the beginning of the data I need to change the first two digits to "01"? Any help would be appreciated. Thanks, Beth beth@integratedresourcemgmt.com

 

[swampBoogie]

UPDATE testnew
SET CustomerID = '01' & mid(CustomerID,3,len(CustomerID)-2)
WHERE testnew.CustomerID Like '00*'
And testnew.CustomerID Not Like '000*'