Update Query, Inserting Variable Number of "0"

[bigchicagobob]

Hello, I'm using MS Access 2010 on windows 7.



I have a table with ~45,000. I have a column with values that each must have (1) two letters followed by (2) six numbers for a total of 8 characters per cell. Somehow numbers beginning with a zero had the zero removed, so I have cells with values like AL12345 instead of AL012345 and ZX234 instead of ZX000234. The data is saved as "text." Does anyone know how I could update the values to include the zeros? I have attempted using Format(), left(), ...etc


Column1 - incorrect format

AL12345
ZX234
AS123456
QW98
PO678345

Column2 - ideal format

AL012345
ZX000234
AS123456
QW000098
PO678345

Cheers,

[June7]

Try:

UPDATE table SET fieldname = Left(fieldname,2) & Format(Mid(fieldname,3),"000000")

Be sure to test with a copy of the table.

[bigchicagobob]

Hey June7,

Unfortunately, it did not work. Do you think an update like this would require using VBA?

Thanks,
Bob

[JoeM]

It worked for me.

How did it not work for you?
Are you getting some sort of error?

How did you try to run it?

Are you sure you updated the table and field name references correctly?

[bigchicagobob]

okay works! thanks again!