I would base the combo box selection off a query rather than a table. If
right now your table has values "SB-MDMD-XX-XXXX", and you know than SB_MDMD-
will always be there, you can use a calculated field in a query to return all
the results with this SB-MDMD- removed. For the record, I'm not an expert in
queries, but I believe this will work...
In query design, you want to create a calculated field, so instead of
drag/dropping a field from the shown table, type into the fieldname (lets
assume we'll name this calculated field NewID, and also assume that the
existing field from the table is named OldID)
NewID: Right([OldID], Len([OldID] - 8))
The Right() function pulls a specified number of characters from the right
of the string. Then Len() function returns the number of characters (length)
of a string. NewID will be the calculated field name, which as you can see
will be calculated by pulling the Rightmost characters from the OldID.
In fact, if the right portion of the format is *always* "XX-XXXX", you could
use:
NewID: Right([OldID], 7)
to pull the 7 digits from the right without needing to calculate the length
of the field.
Then you can either save the query and set it to your rowsource, of if you
like check the SQL view of the query and copy that SQL string into the
rowsource of the combo box. This should give you what you seek.
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
RichW said:
Jack,
Thank you for the advice.
The "SB-MDMD-" is imported to a first table (tblNewSBData). A query then
grabs the changes of the first table to update a second table
(tblExistingSBData). The second table is the row source of the combobox; the
"SB MDMD-XX-XXX" is displayed in the combobox.
How would I remove the "SB-MDMD-" so that it would not exist in the second
table? Would this removal be accomplished during the query?
Thanks again for your help. Truly appreciated.
Ps. I have found a good source concerning Normalization and am reading it
now. Thanks for the recommendation.
The table receives its data from an Excel import into ano; the SB-MDMD- are
imported.
I use a query to update
Jack Leach said:
If SB MDMD- never changes, don't include it in the table. Instead, where you
want to show this, use a calculated field for a query or control source for a
form/report ("SB MDMD-" & [yourfield]).
Saving the redundant data SB MDMD- in your table is not a good practice for
data storage... this is one very painless problems you run into like that,
others ranging from annoyances to impossibilities.
the "XX-XXX" portion of your code implies also that "XX" and "XXX" each have
a special meaning. If such is the case, you should have one table for all of
your "XX"s and one table for all of your "XXX"s, and you should be running a
query to combine the two (calculatedfield: [XX] "-" [XXX]), and here as well
you can prefix your SB MDMD- if you choose.
Do some reading on Normalization for more in depth details about how to
avoid this situation.
hth
--
Jack Leach
www.tristatemachine.com
"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
:
I have a table with a column that contains the following data:
Example: SB MDMD-XX-XXX
The first 8 spaces (SB MDMD-) never change. The last 6 spaces (XX-XXX) are
always different.
I have a form with a combobox that uses the table as its row source. The
combobox currently displays the entire text (Example: SB MDMD-XX-XXX).
I wish to have the combobox displaying only the last 6 spaces (Example:
XX-XXX).
Appreciate any guidance this forum can provide.
Thanks in advance,
Rich
