Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding leading Zeros (Office/Access 2000)

    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?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading Zeros (Office/Access 2000)

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

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding leading Zeros (Office/Access 2000)

    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

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading Zeros (Office/Access 2000)

    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.

    :

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading Zeros (Office/Access 2000)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding leading Zeros (Office/Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •