Query Criteria - Reference Form Combo Box, Return Null

[kagoodwin13]

I have a form with a combo box called cmboESC_LEVEL. The function is for the user to narrow the query to Escalation Level 1, 2, or 3.



In my data, Escalation Level can only determined by 'not null' cells. The two fields are Triaged_To_Level_2 (if "Yes", the record has been escalated to at least Level 2), and Triaged_To_Level_3 (if "Yes", the record has been escalated to at least Level 3).

I am trying to get my query to put criteria on Triaged_To_Level_2 and Triaged_To_Level_3. For Triaged_To_Level_2, I am attempting to use the following code, but it is not working:

Switch([Forms]![frmSEARCH]![cmboESC_LEVEL]="Level 1",Null,[Forms]![frmSEARCH]![cmboESC_LEVEL]<>"Level 1","Yes")

I've tried using nested IIf criteria as well, but the problem seems to be that I cannot return the value of "Null" or "Is Null" as criteria for the field.

The only other thing I could try would be to change the table fields to Yes/No, rather than the current condition of "Yes"/null. Would that work?

[June7]

Why do you need 3 fields for escalation level? Why not one field with 3 value options?

[NTC]

well I think you have a couple issues going on; your post title is that this is the query criteria - - I don't feel IF logic can/should go as criteria. It can go as a calculated field - but that's not the title of your post.... So you want your criteria to be a fixed reference. This helps you trouble shoot your query stand alone.

Put the IF logic in the form as an unbound text box - lets call it "Criteria" ...I would recommend it be:
=IIF(Forms![frmSEARCH].[cmboESC_LEVEL]="Level 1","","Yes")

this way you can view the result to be sure the criteria is correct

then in your query put a simple criteria: Forms![frmSEARCH].[Criteria]

but I'm not sure your null or "" will return all records if that's what you seek.....try it and see - that is a different topic......

[kagoodwin13]

Why do you need 3 fields for escalation level? Why not one field with 3 value options?

Unfortunately that is the way the data has been structured. I am pulling a report from an online database, and using Access to compile reporting statistics.

well I think you have a couple issues going on; your post title is that this is the query criteria - - I don't feel IF logic can/should go as criteria. It can go as a calculated field - but that's not the title of your post.... So you want your criteria to be a fixed reference. This helps you trouble shoot your query stand alone.

Put the IF logic in the form as an unbound text box - lets call it "Criteria" ...I would recommend it be:
=IIF(Forms![frmSEARCH].[cmboESC_LEVEL]="Level 1","","Yes")

this way you can view the result to be sure the criteria is correct

then in your query put a simple criteria: Forms![frmSEARCH].[Criteria]

but I'm not sure your null or "" will return all records if that's what you seek.....try it and see - that is a different topic......

This also didn't work.



SOLUTION:

1. Changed Triaged_to_Level_2 and Triaged_to_Level_3 fields to Yes/No
2. Changed query criteria to this:

Switch([Forms]![frmSEARCH]![cmboESC_LEVEL]="Level 1",No,[Forms]![frmSEARCH]![cmboESC_LEVEL]<>"Level 1",Yes)

For Yes/No fields, you cannot put quotes around the Yes and No.

Thanks for your tips, June and NTC!