Filtering Access Report using multiple Combo Boxes

[jmgibson]

Hi....I have a Form that I've designed which includes 2 Drop Down Boxes and a Button that will generate an Access Report based on the criteria submitted in the drop downs. The purpose of the 2 Combo Boxes (cboDivAllOFFICES, cboCourse_wDiv) is to filter the Report by Division Name (this is a required selection) and then Course Name (this is optional). Obviously, if the user does not select a course from the second combo box, I want the report to query ALL courses for the selected division.

Based on the query I have thus far, it works when the user selects something from both drop downs; however, If the user leaves the 2nd Drop Down blank (Course Name), the query doesn't work. I'm not sure why, but I'm not having luck with the "Like "*"" syntax in the query if the combo box is blank. I've also tried >0 without success. Now, for testing sake, I did specifically define a course number to use (in place of the Like *, to validate that the query would look for different criteria if the Drop Down was null, and it worked and pulled only the courses defined by that course number.

Any thoughts on why the Like * isn't working?

SELECT courseID, empNum
FROM qryCourseRequirements_AllCourses2of2
WHERE (((courseID)=IIf([Forms]![ReportsMain]![cboCourse_wDiv]>0,[Forms]![ReportsMain]![cboCourse_wDiv])) AND ((CompletionDate) Is Null)) OR (((courseID)=IIf([Forms]![ReportsMain]![cboCourse_wDiv] Is Null,(courseID) [b][COLOR=red]Like "*"[/color][/b])))
ORDER BY courseName;

 

[dhookom]

You can't put an operator like "Like" insider the IIf(). Also, it looks like you have one of your IIf()s with only two arguments. Also, there is no mention of cboDivAllOFFICES in your criteria.

Typically the solution for ignoring a combo box that might be null is:

SELECT courseID, empNum
FROM qryCourseRequirements_AllCourses2of2
WHERE 
 (courseID=[Forms]![ReportsMain]![cboCourse_wDiv] Or [Forms]![ReportsMain]![cboCourse_wDiv] Is Null) AND CompletionDate Is Null
ORDER BY courseName;

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

Check out this FAQ faq181-5497. It will create the Where clause for you (no coding involved). Simply copy the code from the FAQ and paste it into a new module. Set your report's RecordSource to "Select courseID, EmpNum From qryCourseRequirements_AllCourses2of2". (Note that your query will not make any reference to the criteria on the form.) Then open the report like this: Docmd.OpenReport "rptName",,,BuildWhere(Me)

The key to making this work is setting the Tag property correctly for each of the fields that represent your selection criteria (this is all explained in the header of the BuildWhere code).

To test it out, after you have set the tag properties correctly, select your criteria and then click a button. In the onclick event of the button put this code: "MsgBox BuildWhere(Me)". The message box should display your where clause, without the word Where.