Solved

Update Query - Pad String with Leading Zeros

Posted on 2002-07-24
3
595 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
Comment Utility
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
Comment Utility
That did the trick!  Thank you so much; the answer had eluded me for several days.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

744 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

16 Experts available now in Live!

Get 1:1 Help Now