IIF statement in Query Criteria

G

Guest

Hi.

I have created a query that uses an IIF statement as the criteria for the
Action field.

Depending on which "Queue" is selected, I want the results to either return
records with an action of OPNT Requested when the queue is OPNTR, or where
the action is Null for any other queue.

This works fine for the OPNT queue but does not return records for any
other.

My criteria looks like this;
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT Requested",Null)

Can anyone help me?

Cheers,
Steve
 
J

John Spencer

You can use a calculated field to search against. Force Null to a zero
length string and search for that. This has adisadvantage - there is no
index available on the calculated field.

Field: SearchThis: Nz([Action],"")
Criteria:
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT
Requested","")

Searching for fields that are equal to null does not work. NULL is never
equal to anything, including Null. To identify fields with null values you
need to use the criteria Is Null.

The following might work. You would need to test.

Criteria: (Is Null AND
[forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]<>"OPNTR") OR
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT
Requested",Null)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

The second solution worked fine! I think I understand what the problem was :)

Cheers,
Steve.

John Spencer said:
You can use a calculated field to search against. Force Null to a zero
length string and search for that. This has adisadvantage - there is no
index available on the calculated field.

Field: SearchThis: Nz([Action],"")
Criteria:
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT
Requested","")

Searching for fields that are equal to null does not work. NULL is never
equal to anything, including Null. To identify fields with null values you
need to use the criteria Is Null.

The following might work. You would need to test.

Criteria: (Is Null AND
[forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]<>"OPNTR") OR
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT
Requested",Null)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

FBxiii said:
Hi.

I have created a query that uses an IIF statement as the criteria for the
Action field.

Depending on which "Queue" is selected, I want the results to either
return
records with an action of OPNT Requested when the queue is OPNTR, or where
the action is Null for any other queue.

This works fine for the OPNT queue but does not return records for any
other.

My criteria looks like this;
IIf([forms]![zSYS_frmWorkflow]![subForm_Display]![cmbSelectQueue]="OPNTR","OPNT
Requested",Null)

Can anyone help me?

Cheers,
Steve
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

parameter with iif statement in query criteria 8
no criteria 3
Date comparison in IIF statement in a query 1
IIf statement in query criteria 5
IIF statement in query criteria, help! 2
IIf Statement in Query Criteria 1
Problem with Criteria 12
IIF statement in query 7

Top