help...query question 2

[mtabt]

I am creating a query with 3 fields for selection criteria: (discipline), (keyword), and (facility). I am using combo boxes in a form to select the records.

Is there a simple query that can do the following:

* select (discipline), (keyword), and (facility) and get records the satisfy ALL 3 criteria.

* select (discipline) and (keyword) and get records that contains BOTH (discipline) AND (keyword) only.

* select (discipline) and (facility) and get records that contains BOTH (discipline) AND (facility) only.

* select (keyword) and (facility) and get records that contains BOTH (keyword) AND (facility) only.

* select (discipline) only and get records that contains (discipline) only.

* select (keyword) only and get records that contains (keyword) only.

* select (facility) only and get records that contains (facility) only.

I want to do this in one step..maybe just a simple query. Is there a way...???

Step-by-step solution will be greatly appreciated.

Thank you very much...

 

[RickSpr]

I'll use MyForm as the name of form that contains the combo boxes, and I'll use cboDiscipline, cboKeywords, and cboFacility as the names of those combo boxes.

1. Create a new query and add the necessary table(s) and/or query(s) you need to it.
2. If you are using multiple tables and/or queries, make sure you draw join lines to connect them all. I'll assume you know how to do this.
3. Drag the fields you need down to the grid. Be sure to include the Discipline, Keyword, and Facility fields. If you don't need one of these to be output from the query, uncheck its Show box.
4. In the Criteria line under Discipline, enter the expression:
=Forms!MyForm!cboDiscipline or IsNull(Forms!MyForm!cboDiscipline)
5. In the Criteria line (the same line as above) under Keyword, enter the expression:
=Forms!MyForm!cboKeyword or IsNull(Forms!MyForm!cboKeyword)
6. In the Criteria line under Facility, enter the expression:
=Forms!MyForm!cboFacility or IsNull(Forms!MyForm!cboFacility)

Any combo boxes on the form that are left blank will have no effect on the selection criteria (because the IsNull() function will return True for these). Note that that means that, if no data is entered on the form, all records will be selected. Rick Sprague

 

[mtabt]

Thank you for your reply, RickSpr. Your instruction is very detail and clear. I will try this as soon as possible. My office is under renovation, I don't think I will have my computer back for another day or two.

Anyway, I really appreciate your help. Thank you.

 

[mtabt]

Thank you again RickSpr.

However, it did not work. The query still returns as if all criteria have to be satisfied. If I only select one of the three, I get nothing. If I only select two of the three, I still get nothing. If I select all three then I get the records that meet all three criteria only. Basically, it still treats it as an "AND" query.

Please help. Maybe I just missing something. Thank you.

 

[smileychickie]

Try staggering your criteria. It has been my experience that if you put them all on the same line it will only pull up records that match all criteria. If you put one on the first line, then in the next box put the criteria in the line below the previous and so on.

 

[RickSpr]

I think it should have worked. Do this: Open your query in Design View, then choose View>SQL View from the menu. Copy the entire SQL statement and paste it here.

You might also want to try changing the criteria to look like this:
=Forms!MyForm!cboFacility or (Forms!MyForm!cboFacility="&quot Rick Sprague