Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Location
    NYC
    Posts
    30

    Unanswered: Create leading zeros in a field

    Hi everyone,

    I have to create a file to send to another office every day.

    Problem:
    I have a number field with a field size of 9. Is there a function that can add leading zeros if the number entered in that field is less than 9 characters.

    Example: 123456 should be 000123456

    I was trying on the Microsoft site search for an example of this but couldn't wasn't able to.

    Thank you

  2. #2
    Join Date
    Nov 2006
    Posts
    13
    The best way to do this would be an update query

    Code:
     
     String(9-Len([FIELD1]),"0") & [FIELD1]
    FIELD1 is your field name

    EDIT: I didnt notice that it was numeric field, this will only work if its a text field
    Last edited by smokinflapjack; 01-15-07 at 17:20.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A numeric field will never store the leading zeros. If changing to a text field isn't an option, simply add them when you view the field on forms/reports. I use:

    Format(FieldName, "000000000")
    Paul

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - just goes to show....

    I favour
    Code:
    RIGHT("000000000" & MyNumber, 9)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2006
    Location
    NYC
    Posts
    30
    Thank you so much!!! I love this place.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If it does, indeed, have a "field size of 9" then it has to be text datatype; number datatypes don't have "field sizes!"
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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