Input Mask for AutoNumber

[BlueHorizon]

Good day!
I would like to create an AutoNumber ID field in a table but set it to begin at a certain number and look like: PJ11001, PJ11002, etc. Is it possible to make this type of input mask work for an AutoNumber?

TIA,


Best,
Blue Horizon

 

[rac2]

No. That may be why they called it an auto number field.

When designing a database just about every table will have a primary key column which uniquely identifies each row in the table. This value has no other purpose than to point to a particular row in the table. Not even the sequence is significant, other than this is an efficient way to create them. The primary key will not be seen by the human eye. As we know, computers are very comfortable with numbers like 11002; conversely they get a little bit nervous with strings like PJ11002. This is what an Autonumber column is used for.

People on the other hand like to see identifiers full of extra information. They will look at PJ11002 and interpret that as a reference to the pajamas Bob wore on October 1, 2002, if you see what I mean.

If you have a PJ11002, then my guess is you plan to have an MC11002 somewhere that identifies an MC instead of a PJ. Instead of building that information into the primary key you should add a column to your table just for that property . It will have values {PJ, MC, WM, etc}. The primary key column can then be an Autonumber column with values like 1,2,3, 11002, 22001, 33012, etc.

When there is a need to display a meaningful identifier for human consumption, concatenate the letters with the number.
SELECT CStr(pk_id) & type_code
FROM MyTable

 

[BlueHorizon]

Thanks, rac2.

Your answer is interesting because I have created an autonumber field with an input mask that contains a letter.

I did this by first deleting any relationships the table has, then changing the data type to text, filling in the input mask with, say, ">"P"0000;0;*, then changing the data type back to autonumber and re-establishing the relationship.

For the most part it works, but I just can't tweak it just the way I want.

So thanks anyway but I'll keep trying.


Best,
Blue Horizon

 

[Remou]

BlueHorizon
I think you may have missed the point that rac2 was making: not that it cannot be done, but that it should not be done, as it is likely to cause problems later.

 

[BlueHorizon]

Thanks, and you may be right - that it shouldn't be done. I teach basic Access skills and am often asked this question when I cover the Input Mask topic. Students don't like the answer that they shouldn't do something. So I thought I'd ask you experts if it could be done. If it could be done, then I would pass that information along with the caveat that it shouldn't be done.

So thanks anyway!


Best,
Blue Horizon