Call a form to display multiple choices

[Guest]

I currently have a form with an unbound text box that I use to search
interchanged part numbers in a database for the entered record. For instance,
I type 01130, press [Enter] and it finds 98765-123 as I want it to. The
database has no duplicate records NOW. I now have a need to add duplicate
records in the search field with similar part numbers: Example: Three fields
used; I can store the NumberID records without the hyphen if necessary.

ProductID NumberID Number
1 01130 98765-123
2 01-130 6549-876
3 0-1130 543-1234
4 011-30 654-9876
I would like to type all numbers as stripped (01130) and have a form that
opens and displays all options above (01130,01-130,0-1130,011-30). I
then want to select the desired record, close the form after selecting and
have it find and display my selection on the primary form. Can
anyone PLEASE help me? Greatly appreciated.

 

[Allen Browne]

Any recent version of Access (2000 onwards) is unreliable when it comes to
searching fields that contain dashes, so this is not a good way to set up
your data. Here is Microsoft's article about the problem, explaining that
the results are inconsistent depending on whether the field is indexed or
not:
http://support.microsoft.com/kb/271661

So, the best solution might be to redesign the NumberID field without dashes
so that you can get reliable results. If the dashes are absolutely
essential, you might have to go back to using an older version of Access (97
or earlier.)

It won't solve this reliablity problem, and it will be inefficient, but you
could temporarily filter it to:
Replace([NumberID], "-", "") = [WhateverYourParameterIsCalled]

If you are using Access 2000, there's actually another bug with trying to
use the Replace() function too:
http://support.microsoft.com/kb/225956