Weird In not Null problem

[Shawny]

I can not get 'Is Not Null' to work in criteria for a tbl created by someone else. It works in another tbl created by this other person, in the same dB. It works on all the tbls I create in that dB. 'Is Null' works just fine everywhere. All the field properties are the same. Allow zero length, required and indexed all are no. Anybody got any ideas?

 

[dynamictiger]

I have had a few cases where this doesn't work in queries and don't know why. However, the workaround that works is to create a new field in the query. Expresion set as Iif(IsNull[MyField],1,0).

Set the criteria for this field to be 1 and you will return all records where [MyField] is null.

HTH

 

[Shawny]

Thanks.
I got the 'Is Null' records using this method. Quite an interesting mystery, this method.
But, the 'Is Null' was working from the start.
I could not get the 'Is Not Null' records to work.

 

[dynamictiger]

Change the criteria to 0 to return not null records.

 

[Rich]

Not Is Null

 

[Shawny]

Thanks guys.
I did try 0 to get 'Is not null'. It pulled up all the records in the tbl, and put a 0 in all the records for the IIf field. The 1 worked: It pulled up the 'Is null' records and put a 1 in the IIf field. This has become a not very needed right now issue, so don't spend much time on this.