not "cl" ?

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
 
J

John Spencer

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
'====================================================
 
M

MGFoster

JethroUK said:
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
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries only return NULLs when you ask for them (unless you ask for
everything "*").

When (not) looking for a string the correct way is to use "<>": <>
"cl", rather than "NOT "cl." The expression '<> "cl"' means a KNOWN
value that is not "cl." The expression "OR IS NULL" means "rows that
also have an UNKNOWN value."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaQya4echKqOuFEgEQJf4wCgxcUSKGjOR1kiRx8oGMEsla4vimIAoN6/
iC42C8cNUPm4PPV282Ur/Hnr
=L/Di
-----END PGP SIGNATURE-----
 
D

Dale Fye

You could use:

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

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
J

John Spencer

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
'====================================================
 
Top