Primary Key with Symbols
in [General]
|
Prev: Click to scroll
Next: Can't outer join or append.
From: Lee Ann on 2 Mar 2010 08:11 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.
From: Paul Shapiro on 2 Mar 2010 08:20 "Lee Ann" <LeeAnn(a)discussions.microsoft.com> wrote in message news:292A3603-83B9-4ED7-94EB-11C54284711A(a)microsoft.com... > 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.
From: Jerry Whittle on 2 Mar 2010 08:57 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Lee Ann" wrote: > 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.
From: Lee Ann on 2 Mar 2010 09:13 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. "Jerry Whittle" wrote: > 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. > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "Lee Ann" wrote: > > > 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.
From: Paul Shapiro on 2 Mar 2010 20:59
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. "Lee Ann" <LeeAnn(a)discussions.microsoft.com> wrote in message news:0DCE0EB0-FD69-4232-90C3-46E7C9ACB57F(a)microsoft.com... > 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. > > "Jerry Whittle" wrote: > >> 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. >> -- >> Jerry Whittle, Microsoft Access MVP >> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. >> >> >> "Lee Ann" wrote: >> >> > 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). |