Searching for item using more than one search criteria

[kabir_hussein]

Hi i am trying to do a query where i can search for a part using more than one criteria i.e. Material description or Part ID.

Does anyone know how i could do this. Many thanks

I was thinking of using the Apply filter button but i reckon there must be a better way of doing this task

thank you

kabir

 

[llkhoutx]

or

I presume you're doing this with a form.

One method is to manually build the RecordSource for the form, depending upon your requisite criteria, e.g.

dim SQL as string

SQL="Select * from tbYourTable WHERE [Description] '" & me!DescriptionCriteria
or
SQL="Select * from tbYourTable WHERE [Description] '" & me!DescriptionCriteria & "' AND [PartID] = '" & me!PartID & "'"

me.recordsource=SQL
me.requery

Note that the "'" delimiters imply text criteria; if numeric criteria, elimate the tic marks.

* of course selects all fields in the table and may not be appropriate; alternatively specify the requisite field in lieu of the *.

Generate you own code to select the appropriate SQL statement, you could potentially have several.

This ought to give you an idea of how to do it.

 

[The_Doc_Man]

Just a thought, and it isn't pretty, but...

Step 1. Build a UNION query. (Look it up in the Help files.)

One leg of the UNION does the description, the other does the part number.

SELECT .... WHERE description = target-description
UNION
SELECT .... WHERE partnumber = target-partnumber;

The SELECT clauses must name the same exact fields in the same order.

Step 2. Build a SELECT UNIQUE query that takes the UNION query as its input. If you have an order-by requirement, it goes here. Name every field here that was named in the UNION query.

Now, open the SELECT UNIQUE query to find what matches either of your two targeted criteria.

Note that this is not quite what you would do if you wanted to also know WHICH field provided the match. You could still do it with a UNION query but the details would differ.

 

[kabir_hussein]

thank you both for your help

much appreciated