Solved

Update Query - Pad String with Leading Zeros

Posted on 2002-07-24
3
608 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
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 50 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.

863 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

21 Experts available now in Live!

Get 1:1 Help Now