Add leading zeros

[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

 

[raskew via AccessMonster.com]

Hi -
Examples from the debug window:

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

HTH - Bob

 

[EllenM]

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

Thanks,
Ellen

 

[raskew via AccessMonster.com]

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

 

[John Spencer]

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
..

 

[EllenM]

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