Add leading zeros

  • Thread starter Thread starter EllenM
  • Start date Start date
E

EllenM

Hello,
I have a field of 1 to 3 digit numbers. I'd like to add leading zeros to
each to make them 6 digits. For instance:
4 becomes 000004
10 becomes 000010
777 becomes 000777

Thanks in advance,
Ellen
 
Hi -
Examples from the debug window:

x = 4
? format(x, "000000")
000004
y = 10
? format(y, "000000")
000010

HTH - Bob
 
Thanks, but could you write an update query for me? I'm not sure how to use
the debug window.

Thanks,
Ellen
 
Ellen -
Assuming that your numbers are stored as numbers, you may want to rethink
an update query. Adding leading zeros returns a string -- true numbers don't
have leading zeros. Perhaps better to just format your form/report fields as
shown in my previous post, leaving them stored as true numbers.

Debug (immediate) window - Ctrl-G

Bob
 
What is the field Type? If it is a number field, then you can't store
leading zeroes.
If it is a text field with number characters then you could use an update
query to permanently change the values.

UPDATE [YourTableName]
SET [YourFieldName] = Format([YourFieldName],"000000")
WHERE [YourFieldName] is not Null AND [YourFieldName] <> ""

If you need help doing this using the design view, please post back for
instructions.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks so much, John!! You're able to speak at my level.

BTW, I STILL use the regular expresssions module you showed me some months
ago. I fact I used it for this project.

Ellen :-D
 
Back
Top