Hi All,
I am working on a database of work orders and recurring tasks. My boss has requested a from with combo boxes for Division, Point of Contact (POC), Month and Year that are then used as inputs for a query. The idea is he can quickly generate custom reports for whatever Division, POC and date that he wants. There is one caveat that is complicating my query. I also included a choice called "<NA>" in each combo box. That way, my boss can leave that box blank to see all the records in that category (i.e. selecting "<NA>" for the month combo box would mean that the query would select records in any month).
All of the tasks and work orders are stored in a query called [qryEventDates]. I am trying to select the tasks based on their due date, called [EventDate] in the query.
The combo boxes are in a form called [Custom Report] and are named:
[ComboDiv]
[ComboPOC]
[ComboMonth]
[ComboYear]
I realized that I could use Like and IIf together to write query criteria that would select the records based on what was selected in the combo box and return all records for that field if "<NA>" is selected. The following expression worked for the Division Field.
Like IIf([Forms]![Custom Report]![ComboDiv]="<NA>",[qryEventDates]![Division],[Forms]![Custom Report]![ComboDiv])
Next, I thought I would apply a similar criterion for the month and year combo boxes with the addition of a DatePart function. My thought was to include the field [qryEventDates]![EventDate] in the query twice and use one to select based on the month and the other to select based on year. (I supposes I could combine them into one field and use an AND statment). I came up with this expression below for the year combo box, but I am having trouble with it. Can anybody help me out? Should I try a different approach? If I can get this one, the criteria for month should be very similar.
Like IIf([Forms]![Custom Report]![ComboYear]="<NA>",[qryEventDates]![EventDate],DatePart("yyyy",[qryEventDates]![EventDate])=[Forms]![Custom Report]![ComboYear])
Thanks for your time.