IIF Expression returning Zero Records in Query

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

BlueWolverine

hello,
MS ACCESS 2003 on XP PRO.

I have a query (obviously) and one of the criteria (in design mode) used to
be:
<>"PDI And <>"PD"

But now I need exception in case I am specifying (on a form) that I mean PDI
or PD.

so I'm trying to write the criteria something like this:

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<> 'PDI' And <> 'PD'"))

This works if either of the conditionals is met, but fails if, say
([Forms]![MainMenu]![g_Route]="Bob and Tom").

Meaning, I can no longer replicate the criteria being <>"PDI And <>"PD"

How can I pass that back to the query as part of the IIF statement? If
there's another way to do it, I'm open to suggestions, but I don't really
want to use VBA for this particular task. Thank you.

Please let me know Thank you!


I have tried all of the following with no luck.

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD",([QMC MASTER LIST].[GQRS CAT]<>"PDI") And ([QMC MASTER
LIST].[GQRS CAT]<>"PD")))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<>'PDI' And <>'PD'")))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<>""PDI"" And <>""PD"""))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD",Not "PDI" And Not "PD"))
 
You cannot use an IIf statement to set conditions like that.

Assuming you're building this query in Access' visual query builder (as
opposed to working directly with the SQL), try specifying the following
conditions on three separate lines:

"PDI" And ([Forms]![MainMenu]![g_Route]="Pre-Drive Checklist"
"PD" And ([Forms]![MainMenu]![g_Route]="Post Drive Checklist"
<> 'PDI' And <> 'PD' AND ([Forms]![MainMenu]![g_Route]<>"Pre-Drive
Checklist") AND ([Forms]![MainMenu]![g_Route]<>"Post Drive Checklist")

If you have conditions on any other fields, you'll have to repeat them on
all three lines.
 
I think I found a more creative solution a little while ago.

I created a new field in the query

Criteria: IIf([Forms]![MainMenu]![g_Route]="Pre-Drive Checklist" And [QMC
MASTER LIST].[GQRS CAT]="PDI",True,IIf([Forms]![MainMenu]![g_Route]="Post
Drive Checklist" And [QMC MASTER LIST].[GQRS CAT]="PD",True,IIf([QMC MASTER
LIST].[GQRS CAT]<>"PD" And [QMC MASTER LIST].[GQRS CAT]<>"PDI",True,False)))


and set its criteria to true. This appears to work flawlessly with good
performance.

Thank you for your idea though, I could see it working nicely.


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Douglas J. Steele said:
You cannot use an IIf statement to set conditions like that.

Assuming you're building this query in Access' visual query builder (as
opposed to working directly with the SQL), try specifying the following
conditions on three separate lines:

"PDI" And ([Forms]![MainMenu]![g_Route]="Pre-Drive Checklist"
"PD" And ([Forms]![MainMenu]![g_Route]="Post Drive Checklist"
<> 'PDI' And <> 'PD' AND ([Forms]![MainMenu]![g_Route]<>"Pre-Drive
Checklist") AND ([Forms]![MainMenu]![g_Route]<>"Post Drive Checklist")

If you have conditions on any other fields, you'll have to repeat them on
all three lines.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BlueWolverine said:
hello,
MS ACCESS 2003 on XP PRO.

I have a query (obviously) and one of the criteria (in design mode) used
to
be:
<>"PDI And <>"PD"

But now I need exception in case I am specifying (on a form) that I mean
PDI
or PD.

so I'm trying to write the criteria something like this:

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<> 'PDI' And <> 'PD'"))

This works if either of the conditionals is met, but fails if, say
([Forms]![MainMenu]![g_Route]="Bob and Tom").

Meaning, I can no longer replicate the criteria being <>"PDI And <>"PD"

How can I pass that back to the query as part of the IIF statement? If
there's another way to do it, I'm open to suggestions, but I don't really
want to use VBA for this particular task. Thank you.

Please let me know Thank you!


I have tried all of the following with no luck.

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD",([QMC MASTER LIST].[GQRS CAT]<>"PDI") And ([QMC MASTER
LIST].[GQRS CAT]<>"PD")))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<>'PDI' And <>'PD'")))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD","<>""PDI"" And <>""PD"""))

IIf([Forms]![MainMenu]![g_Route]="Pre-Drive
Checklist","PDI",IIf([Forms]![MainMenu]![g_Route]="Post Drive
Checklist","PD",Not "PDI" And Not "PD"))
Click to expand...


.
Click to expand...
 
Back
Top