A-2003 - FK combo-box losing formatting when selected in main table

Dave Brydon

Access 2003

I have a combo box in my personnel table, which draws its data from a trade
code table; the original field in the code table, is numeric, Long Integer,
and formatted with 5 zero's [00000]. The FK in the personnel table is also
Long Integer, and formatted as the original field with zero's.

Data Example:

Original: 5, 9, 15, 99, 128, etc.

Formatted (Padded) with zero's:
00005
00009
00015
00099
00128
etc.

When viewed in the combo box, they appear as formatted, which is perfect,
however, when a code is selected, it reverts to its original format,
removing the padded zeros. Again, the FK field is formatted with the 5 zero's.

As all codes are numeric, a number field was perfect, and I stayed with the
default Long Integer, but at this point, if the formatting won't hold, in
the personnel table, perhaps I should go with an Integer field in the trade
code table, and save a few bytes. However, the real issue is, I need the
formatting.perhaps I should use a text field?

Guidance/suggestions welcome!

Cheers,

Dave

Nov 13 '05 #1

Darryl Kerkeslager

"Dave Brydon" <db*****@ns.sympatico.ca> wrote

However, the real issue is, I need the
formatting.perhaps I should use a text field?

You don't really need the formatting. Don't mess with a db done right just
to make it look pretty. If you feel that you must have formatting, use a
hidden id field, and a display field that has been converted to a formatted
string from your id. But again, do you really need that?
Darryl Kerkeslager

Nov 13 '05 #2

Dave Brydon

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote

You don't really need the formatting. Don't mess with a db done right
just
to make it look pretty. If you feel that you must have formatting, use a
hidden id field, and a display field that has been converted to a
formatted
string from your id. But again, do you really need that?

Perhaps I explained my field incorrectly, or the table structure.

I have two tables:

tblMOSID, and tblPersonnel

Structures:

tblMOSID Trade Codes

Name Data Type Description
MOSID_ID AutoNumber Key
MOSID_Code Number Numeric Trade Code Formatted 00000
MOSID_Name Text Trade Code Description
Etc.

tblPersonnel

Name Data Type Description
PersID AutoNumber Key
PSurname Text Persons Name
...
MOSID_Code Number FK from tblMOSID (Formatted 00000)
...

The Lookup field [MOSID_Code] is the Combo Box used to view and select the
Trade Code values form the table tblMOSID

Again, the values look good in the combo-box, but when selected, they revert
to their original digits minus the prefixed zeros; Example: 5 should be
00005.

I tried to store the values in tblMOSID, with the leading zeros, but the
zeros would always be removed automatically, leaving only the number..so,
perhaps I should use a text field instead.thoughts?

Cheers,
Dave

Nov 13 '05 #3

Larry Linson

Numeric values are stored in binary, not in decimal format. The question you
need to answer is: are leading zeros important in any way except appearance?
Formatting those numbers as you have only affects how they are displayed,
not how they are stored or used.

If your database is working correctly, Darryl's advice is good: don't change
it. If you don't like the appearance of the value when displayed, formatting
is a good fix. I am a long-time proponent of "Don't dink with working code."
and that extends to databases.

It is, in fact, quite common to store identifying numbers as text when they
will not be used in calculations or when, sometime in the future, they may
include non-numeric characters. But a change, particularly a change in key
values, can often have more impact than we anticipate.

Larry Linson
Microsoft Access MVP
"Dave Brydon" <db*****@ns.sympatico.ca> wrote in message
news:RL**********************@ursa-nb00s0.nbnet.nb.ca...

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote

You don't really need the formatting. Don't mess with a db done right
just
to make it look pretty. If you feel that you must have formatting, use a hidden id field, and a display field that has been converted to a
formatted
string from your id. But again, do you really need that?
Perhaps I explained my field incorrectly, or the table structure.

I have two tables:

tblMOSID, and tblPersonnel

Structures:

tblMOSID Trade Codes

Name Data Type Description
MOSID_ID AutoNumber Key
MOSID_Code Number Numeric Trade Code Formatted 00000
MOSID_Name Text Trade Code Description
Etc.

tblPersonnel

Name Data Type Description
PersID AutoNumber Key
PSurname Text Persons Name
..
MOSID_Code Number FK from tblMOSID (Formatted 00000)
..

The Lookup field [MOSID_Code] is the Combo Box used to view and select the
Trade Code values form the table tblMOSID

Again, the values look good in the combo-box, but when selected, they

revert to their original digits minus the prefixed zeros; Example: 5 should be
00005.

I tried to store the values in tblMOSID, with the leading zeros, but the
zeros would always be removed automatically, leaving only the number..so,
perhaps I should use a text field instead.thoughts?

Cheers,
Dave

Nov 13 '05 #4

Dave Brydon

Larry,

The formatting was strictly for appearance, we found it much easier to read,
and more consistent in appearance.

Both your advice and Darryl's is sound, and I respect your opinions.
Sometimes we (I) focus on the little things, getting wrapped up in
appearance, and it's often nice to get other views.basically being me back
to the good old KISS, if it ain't broke then don't fix it :-o)

Thanks,

Dave

Nov 13 '05 #5