Multi-input search query

[MrBologna]

Hello!



I have a database that is full of parts and their data (part number, manufacturer, manufacturer part number, description, price)
And I need to create a form/query that filters them based on inputs on the form.

I have already created one of such combos, and it works well because it was standard and easy to do.

This one, however, is mainly used for comparisons.
I need to be able to look up multiple distinct part numbers, or a manufacturer, or a specific part type.

For example, rely100 and rely128 are both relay parts, and should come up along with all other RELY parts when I type RELY into one of the part number fields.
But if I type rely100 into one search field and rely128 into another, I want them both to come up so that I can compare the two.
Basically, I want everything to be an "or" statement.

The issue I'm having is that with the format I used for partial searches before (Like "*" & [Forms]![Parts_Search_Form]![Input_MFG] & "*")
will always return all parts if used in an or statement.

Is there something that allows for ambiguous input without returning all values? Like a "contains" statement?

Any help is greatly appreciated, thanks!

[June7]

Review http://allenbrowne.com/ser-62.html

What do you mean by 'one of the part number fields' - there is more than one? Why?

[MrBologna]

Yes, my initial idea was to use multiple input fields for part numbers to compare them, but inputting multiple separated by commas or something would work as well.

The user needs to be able to pull up more than one part at a time, with just the part number.

[MrBologna]

The results should look something like this, when using the three search criteria in green

[CJ_London]

you could have a text box where the user enters part number separated by a comma, then your filter would be something like

me.filter="[PartNo] IN (" & txtMultiParts & ")"

[MrBologna]

My apologies, but I am quite new to Access. How exactly would I format that?

I tried putting this in the query criteria for the part number:
In ([Forms]![Parts_Search_Form]![Input_PartNo1])

And it only works if I type in 1 exact part number, like RELY300. It does not work for any of the following:
RELY
"RELY"
RELY128, RELY300
"RELY128, RELY300"

[June7]

My question was concerning table design. You have only one part number field? Multiple input controls is different. Probably when you said fields you really meant textboxes.

To use one textbox for input, user would have to enter text values with apostrophe delimiters around each value (if field is text type) as well as separating with comma. Do you really think users will appreciate this? How many might user want to select at one time?

Dynamic parameter array for IN() can't be in query. Must use VBA to set form Filter property.

[CJ_London]

thought you were using a filter, not a criteria. not sure how you would use it in a query