Query needed to strip the first character from a field 1

[JamesBBB]

Hi All,
I have a problem with a query and I just cant seem to work out what to do, I really would appreciate some help. (Im using Access 2003)

I have a text field that contains numbers such as 01 02 03 etc, however some of the values in the field contain numbers such as 010, 011, 012 etc. What I need to do is strip the leading "0" from these three digit numbers, (BTW it is a text field).

I have tried using an update query, but I just dont know how to formulate it and what to put in the update or criteria options.

Any help would eb appreciated as I am completely stumped.
Many thanks

James

 

[Remou]

Perhaps:

Update SomeTable Set SomeField=Right(SomeField,2)
Where Left(SomeField,1)=0 And Len(SomeField)=3

http://lessthandot.com

 

[JamesBBB]

Hi Remou,
Thankyou so much for such a quick repsonse, please forgive my ignorance, I can see exactly what the code is doing that you have supplied, however, how would I apply it in the queries critiera fields or should it be run as an sql statement from a button on a form.

I was trying to create a query using the "Update To:" and "Criteria:" lines in the query and have tried to apply the code without much success.

Again, sorry for my ignorance, but how would I apply this code.

Many thanks
James.

 

[JamesBBB]

Remou

Apologies, I have entered the code in SQL mode on the query and it works perfectly

Absolutlely superb

Many thanks for that

Really appreciated

James