Primary Key with Symbols

[Lee Ann]

Am I correct in assuming I can make a primary key a number data type? When
the user enters the number, it would be without the symbols, however when the
number appears on a report/form, I could made the input mask display it as it
should look (i.e., user enters 000000000001, displays as 0000-000000/01).

Thanks.

 

[Paul Shapiro]

Am I correct in assuming I can make a primary key a number data type?
When
the user enters the number, it would be without the symbols, however when
the
number appears on a report/form, I could made the input mask display it as
it
should look (i.e., user enters 000000000001, displays as 0000-000000/01).

Yes, you can use a long integer for a primary key. It is limited to 9 digits
(+/- 2billion, roughly) so the actual data cannot have as many digits as you
show in your sample. You can display it however you want it on forms and
reports. You might want to try using the format property to create your
preferred display, because an input mask also controls the appearance when
editing. If you need to use the input mask for the display but not when
editing, you could use VBA event code to clear the mask setting when the
textbox gets the focus, and re-set the mask when the textbox loses focus.

 

[Jerry Whittle]

The input mask defines how the data must be entered into the database.
Formatting is how it is displayed. How the data is actually stored is a third
matter. They probably should be the same but don't need to be. In your case
you could have them enter the data as in your example, but format it to
display as you want.

I'm assuming that your primary key has "meaning". I'm in the camp that
believes primary keys should not have "meaning". That's why I almost always
use autonumbers as the PK. If your data must be unique, you could still
enforce that with a unique index.

Another thought: it's going to be quite confusing, maybe even difficult, to
join tables on a PK field that is typed in one way: stored in another; yet
formatted to display in a third way.

 

[Lee Ann]

Thanks Paul and Jerry - the number I wanted to use did have meaning, however,
in light of the issues I may run into in the future, I'll just stick with an
autonumber. If the field is limited to 9 digits, that won't work for me
either.

I appreciate your quick responses to my question.

 

[Paul Shapiro]

If the formatting is part of the data values, then maybe a string would be
more appropriate than a number? That lets you store as many characters as
you need, up to 255 anyway. Things like social security numbers should be
stored as strings, for example, because arithmetic would be meaningless and
the data is really just a set of characters. For social security numbers, I
would store the leading zeroes in the data rather than creating them in the
client interface. Maybe that applies to your data too? You could have an
AfterUpdate event for the textbox that appended the leading zeroes to the
entered data if you're trying to make the data entry easier for the users.

 

[De Jager]

Am I correct in assuming I can make a primary key a number data type?
When
the user enters the number, it would be without the symbols, however when
the
number appears on a report/form, I could made the input mask display it as
it
should look (i.e., user enters 000000000001, displays as 0000-000000/01).

Thanks.