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?
Do both these solutions work equally for text & numbers?
Yes though the second is better and more flexible.
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.
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).
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".
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
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!)