Have you tried the IsNull() function instead the statements.

For example:

Iif(isNull(MYFIELD), ACTION_IF_NULL, ACTION_IF_NOT_NULL)

or for the negative:

Iif(Not IsNull(MYFIELD), ACTION_IF_NOT_NULL, ACTION_IF_NULL)

Hi Donald,

What i am trying to do is, i have this checkbox on Form as default shows as ,  [ ] Is Null.  User can toggle back and forth from showing [ ] Is Null to showing [ ] is Not Null.  This is just visual for user.  But every time User clicks the checkbox it fires a Me.Requery.

The query that fires has a Field: LockedDate.  So the user is suppose to be able to see records having LockedDate field with Null values or with our Null Value.
In Query Criteria in LockedDate column i have,
IIf([Forms]![Frm_C_NewPatient_Annotation_WorkList]![Chk_LockedBy]=True,([dbo_C_NewPatient_Annotation].[LockedDate]) Is Null,([dbo_C_NewPatient_Annotation].[LockedDate]) Is Not Null).  [Chk_LockedBy] object is the checkbox.
I cannot seem to get the correct syntax entered to get this logic to work.
I can create 2 Queries, one for showing LockedDate having Nulls and other query showing LockedDate having Not Nulls.  But i was trying to avoid this and put logic in 1 query instead.  is this possible?

OK, think I figured out what is going on.

If you're trying to use the Query Editor (QBE), it "double" lists the name of your criteria field, just like you showed in your last example and that is why it isn't showing any records.

To fix it, you'll have to edit the SQL directly

If this is the only criteria, it would look something like the following:

SQL WHERE (click to expand)

WHERE
     iif(Forms!Form_C_NewPatient_Annotation_Worklist!Chk_LockedBy,
     dbo_C_NewPatient_Annotation.LockedDate Is Null,v
     dbo_C_NewPatient_Annotation.LockedDate Is Not Null);
Copy

If you have other criteria, just place a comma after the last one and before the semi-colon and then paste this code and if I didn't mystype any of your form/field names, it should work for you.

SQL WHERE without the "v" typo (click to expand)

WHERE
     iif(Forms!Form_C_NewPatient_Annotation_Worklist!Chk_LockedBy,
     dbo_C_NewPatient_Annotation.LockedDate Is Null,
     dbo_C_NewPatient_Annotation.LockedDate Is Not Null);

Copy