Insert 0 into text string 2

[Countymnca]

Hello,
I am sure this is easy, but my brain is fried today. What I need to do is insert a 0 into the following text string 3 places in from the right.

CCNNNNNNNN

C= Character
N= Number

The new format will be CCNNNNN0NNN.

I will only be inserting 0's as we are converting from the old layout into the new.

Thanks in advance...

 

[LyndonOHRC]

is this an update query?
If so,
update tbl set fld=mid(fld,1,7)+'0'+mid(fld,8,3)

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[Countymnca]

Yes it is an update query. The field length can either be 9 or 10 right now as some only have one character at the front. They all have 8 numbers when going from right to left. We are changing to 10 or 11 characters by adding a 0 as mentioned earlier.

 

[Countymnca]

I got it.

UPDATE test SET test.Product_Number = Left([Product_Number],7) & "0" & Right([Product_Number],3)
WHERE (((Len([Product_Number]))=10));

And

UPDATE test SET test.Product_Number = Left([Product_Number],6) & "0" & Right([Product_Number],3)
WHERE (((Len([Product_Number]))=9));

 

[PHV]

Why not a single update query ?
UPDATE test
SET Product_Number = Left(Product_Number,Len(Product_Number)-3) & "0" & Right(Product_Number,3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Countymnca]

That is much better and perfect. Thanks to both of you for your help.

 

[Countymnca]

PHV,
I actually need all three. Yours to convert all the existing data into the new format and the other two to convert new data as it is imported. I dont know when the data that we import will be converted to the new format, so by using the other 2 queries, it will convert them upon import until it is corrected. Once it is correct, the queries will not modify the new data.

Question (though I doubt it will be much touble for you),
I have a search box that upon Exit searches the table for the product number that matches what is in the search box. If it finds it, it retrieves it, if it doesnt find it, it creates a new record using the product number that was entered.

I could change the input mask on that box so that it will only accept the new product number format, but have already had a user request to have the box accept both the new and old format and convert the old format into the new upon exit. I can post the existing Upon Exit code if you would like to see it...