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

Posted on 2011-10-07
Last Modified: 2012-05-12
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.



Question by:tranvangiang17
    LVL 61

    Accepted Solution


    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")

    LVL 33

    Assisted Solution

    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.
    LVL 51

    Assisted Solution

    try this


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

    or you can convert it to str first...

    LVL 51

    Assisted Solution

    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

    Author Closing Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now