• zeros to numbers

    Author
    Topic
    #356798

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

    Viewing 0 reply threads
    Author
    Replies
    • #528693

      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.

      • #528695

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

        • #528740

          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.

        • #529053

          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.

    Viewing 0 reply threads
    Reply To: zeros to numbers

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: