VBA Update query table with leading zero's

[nd0911]

Hello there,

With VBA I want to query a table that have a field with "text numbers", the query purpose is to add a leading zero's to the "text numbers" field from the left side for maximum of 8 digit's.



for example: 1234 will be 00001234, 123456 will be 00123456

Those value's should change in the table itself in the "text numbers" field, so the the "text numbers" field he is the source and he is the target.

Is it possible ?

TNX !!

[jwhite]

You can create an update query to update the column to: String(8 - Len([YourFieldName],"0") & [YourFieldName]

Any time you do something like this to a table, make a backup first in case it doesn't work out as planned.

Although I am curious why you want to "format" the value in the table. The table should hold the value. Formatting is done when you want to display the value in a form or print it on a report.

[nd0911]

Can you please show me the all VBA line of code for it ?

Thank you very much !

[jwhite]

You don't need to do it in VBA. Just create an Update Query in the Query Builder.

UPDATE tblYourTableName Set [YourFieldName] = String(8 - Len([YourFieldName],"0") & [YourFieldName]

Do remember to backup before doing this, just in case...

[nd0911]

Thank you very much it works !!

[jwhite]

Cool. Happy to assist.