472,796 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

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

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
4 2027
"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
"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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Blaine76 | last post by:
Hi All, This may seem like a stupid question but is there a way to keep text formatting when you add it to a database? Such as Paragraphs and line breaks? Any help greatly appreciated. Blaine
4
by: maricel | last post by:
Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel
0
by: Peter D | last post by:
I have a main table and 32 sub tables linked to the main table. In those 32 tables i store info, one of those infos is : wether it is oke ( green - i store a number 3- ), wether it is not oke( -...
0
by: ibeetb | last post by:
Does anyone know how I can preserve my formatting when opening an Excel Based datagrid in my browser using asp.net?
1
by: vishnu2008 | last post by:
Hi, I have a combobox with some comments in it .when we select any one item in combobox all the items should be displayed in textbox. please help me with code. Thanks
9
by: Greg (codepug | last post by:
I have a combobox with the RowSourceType set to Table/Query and the RowSource is an SQL query that references a separate lookup table that contains the data that can be selected in the combobox....
6
by: kstevens | last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
9
mseo
by: mseo | last post by:
hi, I have a form for delete fitered records, i need to delete the main table's record if the child has no records thank you in advance for any help you may provide me
7
by: munkee | last post by:
Hi all, I have 3 memo fields on my main table. I understand that they are susceptible to corruption over network environments. I am looking to move these memo fields in to seperate tables and I...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.