How do I pad spaces to a SELECT field in MS Access?

Posted on 2007-07-28
Last Modified: 2012-05-05
How do I pad left to a field I select so it is always 10 characters long (padded with spaces. So this:


Would become this in my SELECT statment:
      2834 (padded 6 spaces to the left of the number)
  02149803 (padded 2 spaces to the left of the number)
    203985 (padded 4 spaces to the left of the number)
       234 (padded 7 spaces to the left of the number)

I'm looking for something in MS Access where I can do something like SELECT PAD(nbr," ") FROM table. Anything like this exist?
Question by:bemara57
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    You can use the Space() function instead of Pad

    LVL 92

    Expert Comment

    by:Patrick Matthews
    SELECT Right("          " & nbr, 10) AS PaddedNbr
    FROM SomeTable
    LVL 75

    Accepted Solution

    Space(10-Len("2834")) & "2834"

    returns six spaces in front of 2834

    LVL 38

    Expert Comment

    SELECT Format([YourNumber],"0000000000") AS NewNbr FROM [YourTable];

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now