Query by more than one combo box

[NISMOJim]

I am trying to add a feature to a search form. Currently, the form opens a query based on the input from cboCourse1, displaying all of the employees that have passed a training course. The criteria under Course in the query is Like Forms!frmTrng!cboCourse1 & "*". I want to add a second combo box to search if anyone has had both of two different training courses. I tried Like Forms!frmTrng!cboCourse1 & "*" And Like Forms!frmTrng!cboCourse2 & "*" in the criteria, but get a blank datasheet. If it matters, cboCourse2 may be blank and may not be visible most of the time.


How can I show both courses in the same query?

[offie]

Use Or not And.

If it can be blank add "*" & before the Forms! bit

BUT, from your example it seems that the courses are all in the same field, so how is there more than one? Wouldn't they be in different fields, which means that you enter criteria sererately for each field.

[NISMOJim]

cboCourse1 has a dropdown list of all courses available. cboCourse2 also has the same dropdown of those same courses, from the same source. The user may want to know which employees have had "How To Feed Animals Training" by selecting it in cboCourse1, and have also had "How Not To Get Bit While Feeding An Animal Training" by selecting it in cboCourse2. Shouldn't that make it an And statement in the query instead of an Or statement? They are trying to find out who has had both courses, not one or the other.
In most cases, the user will only want to search by one training course at a time, so cboCourse2 will usually be blank.

[ssanfu]

One way I have created search forms is to have the query for the search form return all records. Selecting an option from the combo box would set the form filter property (usually from a button press). If you wanted to add a 2nd selection from a combo box, the code would add the 2nd selection to the filter property.
Involves some code, but more flexible (IMO).
Have a 2nd button to clear the filter and display all records.

[NISMOJim]

Of course I thought this might not be too difficult to do it through the query. Wrong again!

If I put the criteria from the boxes on different lines of the query, thus making it an "Or" statement, this works fine, showing me a record of everyone who has had at least one of the courses selected. Can you explain why it doesn't work when I put the criteria on the same line separated with an "And"? Since I've come this far with it, I was hoping not to have to go back to the drawing board.

Thank you for your time.

[NISMOJim]

Something else I just noticed...
I changed the query criteria back to the way I had it with the "And" separating the 2 comboboxes. With something in both boxes, I get a blank query returned, as I mentioned at the start. If something is in the first box but nothing in the second, it works as planned, showing results for the criteria passed to the query from the first combobox, as I also posted. I just tried it with nothing in the first box, but something in the second, and it is showing the correct records. That leads me to think that it is working, but there is something wrong with the syntax in the "And" part of it, if that makes sense.

[ssanfu]

Would you post the SQL of the query? Easier to see where you are at..

[NISMOJim]

Ya know, I had an epiphany last night on why this isn't working...
As an AND criteria, the query is trying to search for a record that has both training courses on the same record rather than gathering a recordset of both courses. Of course each record only holds one course. If I have one combobox blank and the other with an entry to search by, in either order, it works.
What I may need to do is run 2 seperate queries, one for each combobox, then display the records where the employee name is in both recordsets. I haven't put much thought yet into how to go about doing this, but if I need any help, I know that help is only a keyboard away. Thanks for the help so far!

[shennin202]

Just a wild guess from a novice...
What about using a subquery to select all employees who have the course in cbo1, with the outer query selecting those employees in the list who have taken the course in cbo2? Or vice-versa with the cbo's, not too sure what the sql would look like...