Using Null in query criteria

[TD]

I have a form that has a frame control named fraComplaint. I have a
query that I need to return records that have an empty Date_Closed
field when the value of fraComplaint is 2 and all records when the
value of fraComplaint is 1(the only two values possible for
fraComplaint are 1 and 2).

In the criteria section on the query I put this statement,
"([tblCustomerComplaints]![Date_Closed] Is Null)" ,without the dbl
quotes and it works correctly.

If I use this
statment,"IIf([Forms]![frmReportsExtQlty]![fraComplaints]=2,([tblCustomerComplaints]![Date_Closed]
Is Null),([tblCustomerComplaints]![Date_Closed]))", again without the
dbl quotes, then it returns zero records. Why?

Thanks,
TD

[TD]

Anyone?

[Ron2006]

I will venture in, although no expert.

I made a test query with the first example and then looked at the sql
view of resultant query.

When I attempted the second example the sql view looked entirely
different.

However my test was not exactly like yours so I the parallel may not be
there.

Could you do that and cut and past the two different resulting sql
statements in replay and maybe we can figure out why it is not working
from them.

[salad]

TD wrote:

I have a form that has a frame control named fraComplaint. I have a
query that I need to return records that have an empty Date_Closed
field when the value of fraComplaint is 2 and all records when the
value of fraComplaint is 1(the only two values possible for
fraComplaint are 1 and 2).

In the criteria section on the query I put this statement,
"([tblCustomerComplaints]![Date_Closed] Is Null)" ,without the dbl
quotes and it works correctly.

If I use this
statment,"IIf([Forms]![frmReportsExtQlty]![fraComplaints]=2,([tblCustomerComplaints]![Date_Closed]
Is Null),([tblCustomerComplaints]![Date_Closed]))", again without the
dbl quotes, then it returns zero records. Why?

Thanks,
TD

You could create a calc'd column. Ex:
Expr1:frmReportsExtQlty]![fraComplaints]
In the first 2 criteria rows enter
1
2

Now drop the Date_Closed field into a column
In the first Criteria row, leave it blank
In the second, enter
Is Null

This creates an OR statement (see View/SQL from the menu). You are
saying If Complaints is 1, show all recs. If Complaints is 2, show all
recs where DateClosed is null.

[TD]

You ain't nothing but the man!!! Worked like a charm.
salad wrote:

TD wrote:

I have a form that has a frame control named fraComplaint. I have a
query that I need to return records that have an empty Date_Closed
field when the value of fraComplaint is 2 and all records when the
value of fraComplaint is 1(the only two values possible for
fraComplaint are 1 and 2).

In the criteria section on the query I put this statement,
"([tblCustomerComplaints]![Date_Closed] Is Null)" ,without the dbl
quotes and it works correctly.

If I use this
statment,"IIf([Forms]![frmReportsExtQlty]![fraComplaints]=2,([tblCustomerComplaints]![Date_Closed]
Is Null),([tblCustomerComplaints]![Date_Closed]))", again without the
dbl quotes, then it returns zero records. Why?

Thanks,
TD

You could create a calc'd column. Ex:
Expr1:frmReportsExtQlty]![fraComplaints]
In the first 2 criteria rows enter
1
2

Now drop the Date_Closed field into a column
In the first Criteria row, leave it blank
In the second, enter
Is Null

This creates an OR statement (see View/SQL from the menu). You are
saying If Complaints is 1, show all recs. If Complaints is 2, show all
recs where DateClosed is null.