not "cl" ?

  • Thread starter Thread starter JethroUK
  • Start date Start date
J

JethroUK

I've tried:

not "cl"

<> "cl"

but it wont find some case where field is <blank> or null

Is that right?

I have been using queries for a million years and i alway considered <> "cl"
includes null/empty fields

As a result i have had to use:

Not "cl" Or Is Null

And i've never had to do that before
 
Well either the field was not null - that is it had a zero-length string
in it or you are mis-remembering.

<> "CL" does not match null values and as far as I know it never has.
It does match a zero-length string.

Check the field properties and see if zero-length strings are allowed or
not. If not, then a "blank" field will be null. If so, a "blank field"
could be null or could be a zero-length string.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
You could use:

WHERE NZ([FieldName], "") <> "cl"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
That is true. The other option is to query against a calculated field
instead of against the field itself. The following will work as you wish.

Field: MyField & ""
Criteria: Not "CL"

The disadvantage of this method is that the query cannot use any index
you might have on the field. As a result, the query may be
significantly slower - especially with large record sets.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top