Problems specifying date field criteria in an Access 2000 query...

[Brad Pears]

I have a simple (or so I think Access 2000 query where I am using an
"iif" to specify the "criteria" clause for the field in question... (which
happens to be a date field.) The logic is this... If a field is checked on
the form (frmTest), I want the underlying query to include all records where
the date field in question IS null. If the checkbox is NOT checked, I want
the query to include all records where the date field in question is NOT
null...

The "iif" clause I am attempting to use looks like this....

"iif(forms![frmTest].[chkBox] = true, is null, is not null)"

This is not generating any errors when I run the query but it just does not
work - I get 0 records no matter how the checkbox is set. If I manually
enter the value of "is null" or "is not null" into the criteria area for the
date field, it works fine. The problem seems to be that my "iif" statement
is not resolving to the proper value to be used as the criteria.

As a test, I created a quick little function and I got an error when using
the iif as I coded it above. I tried changed it to include quotes around the
"is null" and "is not null" and although it ran, it did not work either
(makes sense as it would have been looking for those literal values in a
date field.)

I am not sure how this thing should be coded to get what I want. Any ideas?

Thanks,

Brad

 

[Marshall Barton]

I have a simple (or so I think Access 2000 query where I am using an
"iif" to specify the "criteria" clause for the field in question... (which
happens to be a date field.) The logic is this... If a field is checked on
the form (frmTest), I want the underlying query to include all records where
the date field in question IS null. If the checkbox is NOT checked, I want
the query to include all records where the date field in question is NOT
null...

The "iif" clause I am attempting to use looks like this....

"iif(forms![frmTest].[chkBox] = true, is null, is not null)"

This is not generating any errors when I run the query but it just does not
work - I get 0 records no matter how the checkbox is set. If I manually
enter the value of "is null" or "is not null" into the criteria area for the
date field, it works fine. The problem seems to be that my "iif" statement
is not resolving to the proper value to be used as the criteria.

The problem is that you can not use this to choose which
operator is used. Functions can only return a value, not
part of an expression.

You need to include the entire expression so it's value can
be calculated.

iif(forms![frmTest].[chkBox] = true, somefield is null,
somefield is not null)

Now, you have to place that in the WHERE clause of the
query's SQL view so Access doesn't try to compare the value
to the field.

 

[John Vinson]

I have a simple (or so I think Access 2000 query where I am using an
"iif" to specify the "criteria" clause for the field in question... (which
happens to be a date field.) The logic is this... If a field is checked on
the form (frmTest), I want the underlying query to include all records where
the date field in question IS null. If the checkbox is NOT checked, I want
the query to include all records where the date field in question is NOT
null...

The "iif" clause I am attempting to use looks like this....

"iif(forms![frmTest].[chkBox] = true, is null, is not null)"

You can only pass a VALUE from a function - not an operator such as IS
NULL.

Try using a different approach: a criterion of

(Forms!frmTest!chkBox = True AND fieldname IS NULL) OR
(Forms!frmTest!chkBox = False AND fieldname IS NOT NULL)

John W. Vinson[MVP]

 

[Marshall Barton]

I have a simple (or so I think Access 2000 query where I am using an
"iif" to specify the "criteria" clause for the field in question... (which
happens to be a date field.) The logic is this... If a field is checked on
the form (frmTest), I want the underlying query to include all records where
the date field in question IS null. If the checkbox is NOT checked, I want
the query to include all records where the date field in question is NOT
null...

The "iif" clause I am attempting to use looks like this....

"iif(forms![frmTest].[chkBox] = true, is null, is not null)"

You can only pass a VALUE from a function - not an operator such as IS
NULL.

Try using a different approach: a criterion of

(Forms!frmTest!chkBox = True AND fieldname IS NULL) OR
(Forms!frmTest!chkBox = False AND fieldname IS NOT NULL)

John, I admired your expression so much, it inspired a
thought ;-)

How about this for brevity?

Forms!frmTest!chkBox = (fieldname IS NULL)

 

[John Vinson]

John, I admired your expression so much, it inspired a
thought ;-)

How about this for brevity?

Forms!frmTest!chkBox = (fieldname IS NULL)

D'oh!

Elegant indeed, Marahall. Much better!

John W. Vinson[MVP]