Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    zeros to numbers

    I have an autonumber field (don't cringe)counting records I import to a table. I have formatted the field to show the number as 000001, 000002, etc. I now need to isolate individual numbers in a query, in order to do a simple calculation of a check digit, but using Left(), or Mid() only return the #1, 2, and ignores the zeroes. Any ideas on how I can get the query field to return the leading zeroes??

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: zeros to numbers

    Hi,
    You can use the format function in your query to achieve this. If your autonumber field is called ID you could use:
    FormattedID: Format([ID],"000000")
    and it will return a formatted string. You can then use Left, Mid etc. on the FormattedID field.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zeros to numbers

    Rory, Works great, Thanks...........dave

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: zeros to numbers

    But if you want to compare the numbers, why do you care about the zeros? If the field is an autonumber, the zeros aren't really there anyhow, they're just a display format. All you have to do is reference the field to get the actual value in it.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zeros to numbers

    An alternative to converting to string to isolate the individual digits is to make use of mod and (the latter being the integer division operator). That way you're not converting to and from strings all the time to calculate your checksum. The rightmost digit is x mod 10. The second (counting from the right) is (x 10) mod 10 etc.

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Eastern Pennsylvania, Pennsylvania, USA
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zeros to numbers

    Thanks Doug.

Posting Permissions

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