INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Convert numeric to text, zero fill leading spaces by randysmid
Posted: 23 Sep 05

Hi,
It is often necessary to convert numeric fields to text, but you need to have leading spaces replaced with leading zeros.  This often happens when you get data from another system that stores the data in a numeric format.  A good example of this are zip codes.  Another database might be storing the zip code for Boston (MA) as 2124, but it really should be "02124".  If this numeric value were converted to text, we would end up with "2124" (LEFT JUSTIFIED!!!!).  

Here's how to resolve this problem:
1) Make a backup copy of your table.
2) In Table design view, go ahead and convert the format to text and set the appropriate length.  Save the table.
3) In Query, create a new query with the table just modified.  Click on Query and change it to "Update Query".
4) Double-click on the field to be zero-filled.
5) For "Update to", enter code similar to this:  
       Format([zipcode],"00000")
   Note: your fieldname must appear in brackets
6) Click on the Run button (exclamation mark in the top center of the Access window) to update all the records in your table.

HTH, Randy Smith

Back to Microsoft: Access Tables and Relationships FAQ Index
Back to Microsoft: Access Tables and Relationships Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close