• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

How to write a MS Access query to convert a "number" field into "text" field

Since the “Number” field can not have leading zero’s; I have to convert (or update) the original “Numbers” and place them on a new text field; please kindly help me to:

First Issue: How to write a query to add a new text field with 6-Char long to the Test table.  Is it possible with query? or we have to use VBA for this adding?  If NOT, please guide us via VBA code to add 6-char long text field.

Second issue: How to write a query to convert a 4-digit numbers (i.e. “3040”) to 6-Char long text which contain 4-digit values with 2 padded Zero’s (i.e. “003040”)

Please see the attached filed for better picture.



4 Solutions

These really should be two seperate threads.

I'll answer your second question-

If you have a text field, you can use an update query like this to pad it with zeros:

UPDATE YourTable
SET YourField = Format (YourField,"000000")

Number fields can have leading zeroes.

Set the the format to 000000.

If you do want to convert the number to text then you can use Format.

You need to remember though that if you do convert to text then it's possible you might
have problems, for example type mismatch.
HainKurtSr. System AnalystCommented:
try this


where number is the number column in your table...

or you can convert it to str first...

HainKurtSr. System AnalystCommented:
add a new column in table, open mdb, click table, design
add new column, give it name and set to text6

then run this

update mytable
set newcol=Right("000000"+CStr(oldCol),6)

check your data, if all ok,

then go to design again, drop oldColumn, move newColumn to desired order, rename column name to oldCol
tranvangiang17Author Commented:
Hi all:

Thank you.  You guys are my true hero.

I tested and found it is working OK, actually, with all solutions (of mbizup:, imnorie:, HainKurt:)

Until next time.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now