Return records with Null in Field

  • Thread starter Thread starter singing4phun
  • Start date Start date
S

singing4phun

I need to have my query produce the records that are "not check" (or Null)
from a check box field. The original table has a check box which identifies
if the item has been completed (checked if completed, not checked if not
completed).

How do i get my query to only show the records that are unchecked?

I was thinking the IsNull function, but I can't get that to work.

Right now I have no expressions built because the ones I used didn't work.
 
Use 'WHERE checkboxcolumn IS NULL' in your SQL
It's quite possible for a checkbox column to contain a NULL, so ignore what
the previous poster said.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
The "'WHERE checkboxcolumn IS NULL' " is not working. I have been trying this
already.

Maybe I'm typing the expression wrong. Can you give me a string expression
example?
 
singing4phun said:
I need to have my query produce the records that are "not check" (or
Null) from a check box field. The original table has a check box
which identifies if the item has been completed (checked if
completed, not checked if not completed).

How do i get my query to only show the records that are unchecked?

I was thinking the IsNull function, but I can't get that to work.

Right now I have no expressions built because the ones I used didn't
work.
Click to expand...

Just because it is not checked does not mean it contains Null. It could
contain Null but it could also contain 0 (False or No).

So you need to test for both conditions: where field is null or field=0
 
Thanks, I have written codes for both "IsNull" and the "0", but its still not
working. I'm sure I'm not writing the string correctly if neither of these is
working. Can someone provide an expression example?
 
OK, you are going to have to provide more detail. First off, explain
what you mean by "not working": error message? incorrect results? If the
latter, how do you know the results are incorrect? If the former, switch
your query to SQL View so you can show us the sql statement that is
generating the error message.

What I posted previously IS the sql expression you need to use in your
query (correcting the name of the field of course).
 
Ok, when I run it with the below SQL(s) I get "All" records, both checked and
unchecked....I only want unchecked or rather the "no" records from a "Yes/No"
check box field.

The SQLs I have used:

WHERE (([UT Field Rep Tagging]![Added to Define]=Null) AND (([Daily Tagging
Report].[Date Tagged]) Between [Start Date] And [End Date])) OR ((([Start
Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used
WHERE (([UT Field Rep Tagging]![Added to Define]=0) AND (([Daily Tagging
Report].[Date Tagged]) Between [Start Date] And [End Date])) OR ((([Start
Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used:
WHERE (([UT Field Rep Tagging]![Added to Define]="0") AND (([Daily Tagging
Report].[Date Tagged]) Between [Start Date] And [End Date])) OR ((([Start
Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

Also:
WHERE (([UT Field Rep Tagging]![Added to Define]="No") AND (([Daily Tagging
Report].[Date Tagged]) Between [Start Date] And [End Date])) OR ((([Start
Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

So, what am I doing wrong?
 
To troubleshoot this yourself in the future, I recommend that you
concentrate on the Yes/No field. Remove everything else from the
criteria. Get the query returning the "unchecked" records, then work on
the date fields. However, read on:
Ok, when I run it with the below SQL(s) I get "All" records, both
checked and unchecked....I only want unchecked or rather the "no"
records from a "Yes/No" check box field.

The SQLs I have used:

WHERE (([UT Field Rep Tagging]![Added to Define]=Null) AND (([Daily
Click to expand...

Nobody has recommended using "=Null". We have all explicitly said "Is
Null". A field will never "=" Null
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used
WHERE (([UT Field Rep Tagging]![Added to Define]=0) AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used:
WHERE (([UT Field Rep Tagging]![Added to Define]="0") AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

Also:
WHERE (([UT Field Rep Tagging]![Added to Define]="No") AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

So, what am I doing wrong?
Click to expand...

Well, you're not following my suggestion, which was to check for both
conditions. I assume [Added to Define] is the relevant field, correct?
So go into SQL View and replace your WHERE clause with this:

WHERE ([UT Field Rep Tagging].[Added to Define] is Null Or [UT Field Rep
Tagging].[Added to Define]=0)

Run it. Does it retrieve the expected records? If so, add the date
criteria back in.
 
I figured it out thank you.

Bob Barrows said:
To troubleshoot this yourself in the future, I recommend that you
concentrate on the Yes/No field. Remove everything else from the
criteria. Get the query returning the "unchecked" records, then work on
the date fields. However, read on:
Ok, when I run it with the below SQL(s) I get "All" records, both
checked and unchecked....I only want unchecked or rather the "no"
records from a "Yes/No" check box field.

The SQLs I have used:

WHERE (([UT Field Rep Tagging]![Added to Define]=Null) AND (([Daily
Click to expand...

Nobody has recommended using "=Null". We have all explicitly said "Is
Null". A field will never "=" Null
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used
WHERE (([UT Field Rep Tagging]![Added to Define]=0) AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

I've also used:
WHERE (([UT Field Rep Tagging]![Added to Define]="0") AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

Also:
WHERE (([UT Field Rep Tagging]![Added to Define]="No") AND (([Daily
Tagging Report].[Date Tagged]) Between [Start Date] And [End Date]))
OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))
ORDER BY [Daily Tagging Report].[Date Tagged];

So, what am I doing wrong?
Click to expand...

Well, you're not following my suggestion, which was to check for both
conditions. I assume [Added to Define] is the relevant field, correct?
So go into SQL View and replace your WHERE clause with this:

WHERE ([UT Field Rep Tagging].[Added to Define] is Null Or [UT Field Rep
Tagging].[Added to Define]=0)

Run it. Does it retrieve the expected records? If so, add the date
criteria back in.
Click to expand...
 
Back
Top