Using more than two IIF in query shows blank

[naeemahmad]

Hi Experts, Been looking for a solution (or reason why) to this can seem to find one online..

I have a query with many fields, but focusing on the below 4 fields:
[EMPLOYEE_ID]
[FLASHER_NUMBER]
[SIGNAL_NUMBER]
[LIGHT_NUMBER]

I am using the following IIF criterion syntax for each field:
Like IIf(IsNull([Forms]![frmREPORTS]![Combo Name]),"*",[Forms]![frmREPORTS]![Combo Name])

The above work great with two field criteria's, but when I use the above syntax with a third (or more) field... the query returns blank.

My SQL syntax using three fields ;
SELECT DISTINCTROW tblTIMESHEET.SYNC_DATE, tblTIMESHEET.UNQ_LINK_ID, tblTIMESHEET.LOG_DATE, tblTIMESHEET.LINK_ID, tblSIGNALS.DEVICE_TYPE, tblTIMESHEET.EMPLOYEE_ID, tblSIGNALS.FLASHER_NUMBER, tblSIGNALS.SIGNAL_NUMBER, tblSIGNALS.LIGHT_NUMBER, tblTIMESHEET.PROJ_CHARGE_NUMBER, tblTIMESHEET.MAINT_LOG_PENDING, tblTIMESHEET.PROJECT_DESCRIPTION, tblTIMESHEET.TASK_ID, tblTIMESHEET.TRCD, tblTIMESHEET.PC, tblTIMESHEET.HOURS, tblTIMESHEET.VEH_ID, tblTIMESHEET.VEH_PC, tblTIMESHEET.VEH_TRCD_ID, tblTIMESHEET.VEH_HOURS, tblTIMESHEET.MAINT_INITIATED_DATE, tblTIMESHEET.ML_WORK_DESCRIPTION, tblTIMESHEET.ML_PENDING_COMMENTS, tblTIMESHEET.ML_CLOSE_COMMENTS
FROM tblTIMESHEET LEFT JOIN tblSIGNALS ON tblTIMESHEET.LINK_ID = tblSIGNALS.LINK_ID
WHERE (((tblTIMESHEET.EMPLOYEE_ID) Like IIf(IsNull([Forms]![frmREPORTS]![Combo26]),"*",[Forms]![frmREPORTS]![Combo26])) AND ((tblSIGNALS.FLASHER_NUMBER) Like IIf(IsNull([Forms]![frmREPORTS]![Combo32]),"*",[Forms]![frmREPORTS]![Combo32])) AND ((tblSIGNALS.SIGNAL_NUMBER) Like IIf(IsNull([Forms]![frmREPORTS]![Combo28]),"*",[Forms]![frmREPORTS]![Combo28])) AND ((tblTIMESHEET.MAINT_LOG_PENDING)=True))
ORDER BY tblTIMESHEET.LOG_DATE;


Can anyone please provide some insight into whats happening or some guidance. I know there are other approaches, but just want to see why this behavior occurs.

Thanks a bunch in advance !!!



Regards

[aytee111]

Do they work if you do them one at a time? It may be that only one of them is causing the problem.

[naeemahmad]

Yes works alone.. also works great with same criteria in two columns... but when I add the same criteria syntax in a third column(field).. I get nothing.. a blank query result.

Thanks for the follow-up

[aytee111]

If you would like to attach a sample database we could take a look.