Query not working???

[ekta]

Hi:

I have a query that has 4 criteria's. 2 criteria's have been added to the query and the other two are entered by the user. For some reason when I run the query it is not taking into consideration the criteria added to the query.
Can anyone please take a look at it. I am attaching the db.

Thanks,

Ekta

 

[ekta]

Another thing I forgot to mention. The 2 criteria's added in the query are compulsory while the criteria's entered by the user are optional.

 

[Jon K]

I have changed the Row Source of the combo box and added a new query. I have also added a record for the year 2003 in the table for testing.

Since your table contains Null values in both BusinessArea and WorkStart, I take it that when the combobox and textbox on the form are left blank, you want the query to return every record including the Null values in the two fields. My query uses these two IIF expressions for the two optional criteria:-

IIf(IsNull([Forms]![frmExecutive]![txtYear]), True, Year([WorkStart])=[Forms]![frmExecutive]![txtYear])

IIf(IsNull([Forms]![frmExecutive]![cboBusinessArea]), True, [BusinessArea]=[Forms]![frmExecutive]![cboBusinessArea])


However if you want the query to return only those recorods in which the field values are Null when the boxes are left blank, you can change the two IIF expressions to;-

IIf(IsNull([Forms]![frmExecutive]![txtYear]), IsNull([WorkStart]), Year([WorkStart])=[Forms]![frmExecutive]![txtYear])

IIf(IsNull([Forms]![frmExecutive]![cboBusinessArea]), IsNull([BusinessArea]), [BusinessArea]=[Forms]![frmExecutive]![cboBusinessArea])

 

[ekta]

Thanks Jon. This is perfect.