Adding leading Zeros (Office/Access 2000)

Topic

New Reply

I imported a field with 7 numbers. But the records have some with only 4 or 3 digits. I need to add leading zero’s so that the whole column will be uniformed.

IE:
0000123
1234756
0000006
0023875
0784623
1930247

How can I do this?

Reply | Quote

Replies

You can try and use the attached code if you know what length the result string should be. Usage is as follows:

varString = PadZero(“StringToBePadded”,10)

where 10 is the length of the result string.

Reply | Quote

Why don’t you leave the field defined as a Long Integer. Any time you wish to show the field as 7 digits (with leading zeros) thenjust use the format command.
Pat

Reply | Quote

Thanks guys but i figured it out.

Field: CostCent

In a query I put this: FullNum:Right(“000000″&[CostCent]),7)

Basically I made the query add 6 zeros then took the 7 digits from the right to left.

so:

12
34555
456789
34
1

Becomes:
00000012
00000034555
000000456789
00000034
0000001

Then taking the last 7 digits:
0000012
0034555
0456789
0000034
0000001

I simply created a Make Table Query to capture the numbers for later use.

Thanks and I hope this helps someone else.

:

Reply | Quote

There is a difference between formatting numbers and the number stored in the DB (a mistake that trips many people up until it bites them one day).

This looks like an ID of some kind, if not the following probably doesn’t apply.
eg. display: 0123, filter on ID=0123, records found = 0 ! Is that what you require?
If the field is a field that may be filtered on then you are best to store the ‘numbers’ as text fields and pad them as you do already OR construct your queries to strip out the leading zeros. The second method is no good if an ID of 123 and an ID of 0123 are both valid.

Reply | Quote

Thanks for your concerns Andy. The numbers are a type of ID. But it was imported from Excel. It originally came out of a mainframe program and the operator stripped out the leading Zero’s. The ID starts from #1 to 9999999. I asked him to keep the zeros for the next export, but in the mean time I was thinking up a way for Access to handle it.

I am happy with the fix a created. Although its a two step process, it can be wrapped up in a macro.

Reply | Quote