No return on Null values

[forrestapi]

I just want to check and see if this is normal behavior.

I have a query that with no "criteria" returns 1294 records. In one of the columns(lets call it Status) i have chosen to exclude the criteria "cancled". There are 49 records that this should exclude and leave a total of 1245. Instead i get 106. After some digging through i have realized what the problem is. When the query runs it is also excluding any records with a null value in the Status Column (there are a total of 1188 of these records). I have verified this is the issue by including "Is Null" in the "Or" section of the "Status" column. When doing this, it correctly displays the 1245 records.

So what im wanting to know is, is it normal behavior for a query to not return results on a record if the criteria you are using is bounced against a "null" field.

Any help would be great, Thanks!

[NTC]

you write: i have chosen to exclude the criteria "cancled".

(ignoring the typo)

'exclude' ??? so your criteria is everything that is NOT = "canceled"?? what syntax do you use....because criteria shows everything that IS (i.e. "canceled")

if one just puts that word in the criteria field.... your 'criteria' is that it must say 'canceled'...thus a null would not satisfy that criteria.

hope this helps.

[slave138]

The actual query would go a long way to figuring out if it's doing something unusual...

[forrestapi]

you write: i have chosen to exclude the criteria "cancled".

(ignoring the typo)

'exclude' ??? so your criteria is everything that is NOT = "canceled"?? what syntax do you use....because criteria shows everything that IS (i.e. "canceled")

if one just puts that word in the criteria field.... your 'criteria' is that it must say 'canceled'...thus a null would not satisfy that criteria.

hope this helps.

Im sorry I left out some detail the syntax entered in the critera field is actually" <> canceled" . But yes I know without that syntaxit would only pull like items, not unlike.

The actual query would go a long way to figuring out if it's doing something unusual...

I agree, however I left the query at work and am currently at home. If no one can make sense of this I will try and post the query first thing monday.

[weekend00]

Please remember, null neither equal (=) any value, nor (not equal)(<>) any value, even doesn't equal another null. So:
null <>"canceled" return false
null ="canceled" return false
null = null return false ( we can put null incritia cell to catch the null values because Access translate it to "is null", not "=null", if we put "=null" there, we can not catch anything)

If you also want the records with null, put <>'cancel' or is null as the criteria.