Problem with IIF in a criteria query

[Joefulmer]

Want to use an immediate if in identifying the criteria for a field in a query.

There's a check box called, for example, Box1, on on an open form called FOrm1. From this form we click an option to open form2 with an underlying query called Query1.

The criteria for a field called Field1 says "If Box1 is not checked then filter the form to EXCLUDE records with "North" in Field1. If it is checked then select records with "South" in the field.

I made the criteria iif this way: iif(Forms![form1].[box1]=false,Not "north","South"). IF the box is checked, I get "South" records. If it is not checked I get no records even though there are records with "North".

Something seems not to work if I use the Not (or if I use <>). If I remove the not, and have "North","South" then I get either just North records or just South records.

If I remove the IIF and use just Not "North", I get all "Not North" records.

Any ideas why embedding the Not would cause the criteria not to work?

 

[Jon K]

I made the criteria iif this way: iif(Forms![form1].[box1]=false,Not "north","South"). IF the box is checked, I get "South" records. If it is not checked I get no records
...........................
Any ideas why embedding the Not would cause the criteria not to work?

When you put an IIf in the criteria row, Access would internally put an equal sign in front of IIf. So when [box1] is false, the criteria became [Field1] =Not "north". Since =Not is not a proper operator, you would get no records. (Similarly for [Field1] =<> "north".)


To avoid the problem, you can put the criteria in a new column in the query grid like this:-
---------------------------------
Field: IIf([Forms]![form1]![box1]=True, [Field1]="South", [Field1]<>"North")

Show: uncheck

Criteria: True
---------------------------------

If [Field1] contains null values, you can include them in the IIf like this:-
IIf([Forms]![form1]![box1]=True, [Field1]="South", [Field1]<>"North" Or [Field1] Is Null)


Note:

....... "If Box1 is not checked then filter the form to EXCLUDE records with "North" in Field1. If it is checked then select records with "South" in the field.

The criteria will always exclude records with "North".
.

 

[Joefulmer]

Thanks Jon K..one more question

This works well. NOw, how about if for the true part I want all records, for the false part the <> records?

 

[Jon K]

IIf([Forms]![form1]![box1]=True, True, [Field1]<>"North")


If [Field1] contains null values:-
IIf([Forms]![form1]![box1]=True, True, [Field1]<>"North" Or [Field1] Is Null)
.

 

[Joefulmer]

Thanks Jon K..Awesome help

Thanks for your help