Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003

    Unanswered: Have same number of characters in field

    I have an ID field that I need to have the same length of characters.
    We use the school's student IDs in our system. The school send us a spreadsheet with their IDs and grades but the IDs vary from 4 to 7 numbers. For consistency I want them to be all 7 numbers in length. How can I add zero in front of the IDs that are shorter than 7 numbers?

  2. #2
    Join Date
    Apr 2006
    try this:

    if Len(ID) = 4 then
    ID = "000" & ID
    else if Len(ID) = 5 then
    ID = "00" & ID
    else if Len(ID) = 6 then
    ID = "0" & ID
    Only quitters quit!

  3. #3
    Join Date
    Dec 2004
    I always had trouble with nested if statements.
    I found this to be easier to follow:


    It doesn't matter how many characters the original field has. It adds a bunch of leading zeros and then takes the last seven characters from the built-up value.

  4. #4
    Join Date
    Jun 2006

    Thumbs up

    Hi, syntaxerror & trowe,

    Do both these solutions work equally for text & numbers? I have a FileNumber field (number, but not the primary key) with format "0000" that I have to change to text now to accommodate alphanumeric.

    Also, is this only a display format or does it make the actual ID 7 digits long? Would your code go in the format of the table field, or in the form?

    Thanks for the help!

  5. #5
    Join Date
    Feb 2004
    One Flump in One Place
    Quote Originally Posted by Lungta
    Do both these solutions work equally for text & numbers?
    Yes though the second is better and more flexible.
    Quote Originally Posted by Lungta
    Also, is this only a display format or does it make the actual ID 7 digits long?
    Depends how you use the expression.
    Quote Originally Posted by Lungta
    Would your code go in the format of the table field, or in the form?
    Personally I would adjust your table to store the data as "0001234". I don't consider this to be a presentation issue although you could treat it as one if you wanted. You would have to change the field type to text and I would have some sort of validation (####### for seven numbers).

    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2006

    Format or validation rule for field in table

    Sorry, didn't mean to go on here...

    Changed the field type to Text in table Design view, but am not getting any leading zeros no matter what. I've tried pretty much every combination of input mask, format 0000 or ???? or 9999, validation rule "????" (it wouldn't accept #### as validation rule).

    Would like to store the leading zeros but don't want anyone to have to type them in as the data entry form automatically increments the numeric portion. Where exactly would I specify the format or validation for this?

    We're also using an input mask, not stored, so it would look like "RVA-0023".

  7. #7
    Join Date
    Apr 2004
    outside the rim
    Based on your last post, you want to use the value as a genuine number, but display it as an alpha-numeric with a specific format of the data.

    So, that's exactly how I would do it: store the actual data in the table and format the data for the humans.

    Store the number in the table as a number - don't worry about leading zeros or any of that other jazz. You can format the number either at the query level (probably better, but depends on the rest of your app) or at the form and report level.

    Your form can auto increment on the numberic value (in this a case for "Autonumber"?) and then display the formatted version. In your lowest level select query, add the formatted version so that all subsequent forms, queries and reports have BOTH to choose from. I'd use txtValue:"RVA-" & Format(ID,"0000000") in the query, but the other options work as well.

    I deal with this at work: tool numbers are a sequentially assigned number like 1025, but have the format "8T01025". The humans actually prefer simply typing in the numeric portion in the combo boxes, but want the formatted version to appear on screen and in print

    good luck,

  8. #8
    Join Date
    Jun 2006
    Thanks for the reply. I should have been clearer that that was what we've been doing until now, based on the business needs we expected to have. Forms & reports/letters displayed the right format so no complaints.

    Now we've just inherited a bunch of older files to handle and most of them carry pre-existing alphanumeric file numbers - WessmanMiller2005, stuff like that. We can certainly continue to assign our own file numbers with the format we've been using and just keep track of their alphanumeric references in a separate field. But it would be ideal if the file numbers could be visibly different for old & new files (different contractual arrangement, different pay rates, etc.- helps invoice reviewers avoid errors). So I'm trying to find out whether I can have a text field that supports the look (input mask, leading zeros) of the new numbers and still accommodates alphanumeric.

    So far I'm striking out on the leading zeros if I make it a text field. It's not the end of the world if it's not doable, I'm just wondering if any of you gurus out there have ways to accomplish it. (We're learning tons from these posts & your numerous takes on different questions!)
    Thanks muchly,

Posting Permissions

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