Query using the or criteria

[dsellers1]

In my query, I am looking to set the criteria by having the user pick 1 of 3 options, or a combination of those three options. Here's an example:

The fields are Manager, Employee, Job Function. Let's also say that my criteria is David, John, Busy Work. I want to be able to pull results by David, John, Busy Work, or a combination of those three.

My Query for picking 1 of the 3 looks like this:

Field: Manager Employee Job Function
Criteria: David
Or: John
Busy Work

When I try to add additional Or statements, it never works. An example would be in the fourth line of the criteria, I add:

David John

Running the query produces only the results for David. Mixing it up produces only the results from the first item in the criteria (Manager over Employee over Job Function). :banghead:

Any help on this one?

 

[Brianwarnock]

Use a form with text boxes to supply the criteria

Eg searchform

Txtmanager
TxtName
Txtfunction

Then switch your query to SQL view to add the criteria ie the Where clause

Where (manager=forms!searchform!txtmanager or forms!searchform!txtmanager is null)
And (Employee=forms!searchform!txtname or forms!searchform!txtname is null)
And (JobFunction=forms!searchform!txtfunction or forms !searchform!txtfunction is null)

Save the query whilst in SQL View, it looks a mess when saved from the design grid view.

Notice that I have no space in Job Function, if you have spaces in your names you will need to enclose them in [], it is best only to use alpha numerics and the underscore.

The above will enable you to select by any combination of 1 to 3 fields by having no entry in the selection that you are not selecting on.

Note that the text boxes could be combo boxes with data supplied from the table.

Brian

 

[dsellers1]

Duh! Haven't had to use SQL in so long, I forgot about it. That worked perfectly. Thanks for the help, Brian!