Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Query - Pad String with Leading Zeros

Posted on 2002-07-24
3
Medium Priority
?
645 Views
Last Modified: 2010-07-27
I am trying to write an update query that will convert all of my records in a field to 14 digits with leading zeros (0).

So, instead of
19750
745513265

I would like
00000000019750
00000745513265

In Excel, I do it using =Rept("0",14-Len(B1))& B1, but cannot figure out the syntax in Access.

This must be simple, but I am tearing my hair out.  Please help.  Thanks.
0
Comment
Question by:lstaple
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
ID: 7175723
You don't really need this as you can always use the format statement to "mimic" this behaviour.

But you can also use it to transform the field.
First add a text field as I assume it's now numeric..

Then use =format([fieldname],"00000000000000") in an update query to get the field transformed.

Nic;o)
0
 

Author Comment

by:lstaple
ID: 7175766
That did the trick!  Thank you so much; the answer had eluded me for several days.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7175806
Then next time use EE sooner ;-)
I personally stop working on a problem when it takes longer as one hour. Then it's time to ask a collegue (or EE)

Success with the application !

Nic;o)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

609 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