Query filtering out more rows than the Criteria I set (filtering out "blank" rows).

[David Ball]

Query filtering out more rows than the Criteria I set (filtering out "blank" rows).

Hi,

I am trying to filter a field value out of a query. The Field is called Discipline and the value I want to filter out is called IND.
In the criteria row for field Discipline in the query I have <> ”IND”.
The problem is it is filtering out IND but also any rows where there is no value in the Discipline field.
However, when I set my criteria row to “IND” the query returns only rows with the value IND, not those with no value.
How can I get this to work properly?
Thanks very much.

Dave

 

[arnelgp]

Re: Query filtering out more rows than the Criteria I set (filtering out "blank" rows

Where Left(Nz(Discipline, "xxx"), 3) <> "IND"

 

[The_Doc_Man]

Re: Query filtering out more rows than the Criteria I set (filtering out "blank" rows

If Arnel's suggestion works, your problem was that Nulls don't compare very well. Whatever you wanted them to do or to be, they won't cooperate. So those items have have NO values probably have Null values (if Arnel's suggestion works.)

 

[David Ball]

Re: Query filtering out more rows than the Criteria I set (filtering out "blank" rows

Thanks very much, guys

 

[Minty]

Re: Query filtering out more rows than the Criteria I set (filtering out "blank" rows

The normal way around this is to add an OR Is Null to your criteria if you want to include Null Values.

As the Doc says Null doesn't behave well with logical comparisons.

If you open the immediate window and type ? Null + False and hit return it will return Null. Whereas you might expect it to return False... Try some other values / combinations to see the result.