VBA SQL Where - "<>'False'" vs. "Is Not Null"

  • Thread starter Thread starter wdsnews
  • Start date Start date
W

wdsnews

I typed a SQL query into Access using "Where [Home Phone] Is Not Null".
When I next looked at it, the query said - Where [Home Phone]<>"False". My
intention is to move the SQL statement into my VBA code.

What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and I'd prefer to
use the "Not Null".
 
In
wdsnews said:
I typed a SQL query into Access using "Where [Home Phone] Is Not
Null". When I next looked at it, the query said - Where [Home
Phone]<>"False". My intention is to move the SQL statement into my
VBA code.
What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and I'd
prefer to use the "Not Null".
Click to expand...

That's odd, and I can't reproduce it with a simple query. What exactly
did you do, and what is the data type of the [Home Phone] field? And
what version of Access are you using?

If [Home Phone] is a boolean (Yes/No) field in a table in an Access
(Jet) database, then it can *never* be Null as stored in the table.
Boolean fields are stored in a way that doesn't allow Nulls. The only
way a boolean field in a query can be Null is in an outer join, where
the field comes from a table that had no matching records.

So maybe Access is trying to fix up your query to "do what you meant"
somehow; however, as I said, I couldn't reproduce it. I'm using Access
2002, so maybe in a later version Access is being more "helpful".
 
They definitely aren't the same thing.

Is Not Null is the correct syntax: the second syntax would look for the
literal string "False" in the telephone number field.
 
Thank you for your reply. I'm glad to know this is not
normal. I'm using Access 2003, and it's possible that I
mistyped something that caused Access to replace my 'Where'
statement with its own? hmmm. Perhaps wrong placement or
number of parenthesis? I don't know. [Home Phone] is a text
field.

Now I've gone back in to the query and retyped "Is Not Null"
to replace '<>False'. This time Access kept it and all looks
as expected. So now I'm curious.
Click to expand...

Menu to Tools->Options->general and make sure that the
checkboxes in Name AutoCorrect are NOT checked. It sometimes
does things like this, and a lot worse: the feature is
deservedly referred to as Name AutoCorrupt by several posters
here

Dirk Goldgar said:
In
wdsnews said:
I typed a SQL query into Access using "Where [Home Phone] Is
Not Null". When I next looked at it, the query said - Where
[Home Phone]<>"False". My intention is to move the SQL
statement into my VBA code.
What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and
I'd prefer to use the "Not Null".
Click to expand...

That's odd, and I can't reproduce it with a simple query.
What exactly did you do, and what is the data type of the
[Home Phone] field? And what version of Access are you
using?

If [Home Phone] is a boolean (Yes/No) field in a table in an
Access (Jet) database, then it can *never* be Null as stored
in the table. Boolean fields are stored in a way that doesn't
allow Nulls. The only way a boolean field in a query can be
Null is in an outer join, where the field comes from a table
that had no matching records.

So maybe Access is trying to fix up your query to "do what
you meant" somehow; however, as I said, I couldn't reproduce
it. I'm using Access 2002, so maybe in a later version
Access is being more "helpful".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Thank you for your reply. I'm glad to know this is not normal. I'm using
Access 2003, and it's possible that I mistyped something that caused Access
to replace my 'Where' statement with its own? hmmm. Perhaps wrong
placement or number of parenthesis? I don't know. [Home Phone] is a text
field.

Now I've gone back in to the query and retyped "Is Not Null" to replace
'<>False'. This time Access kept it and all looks as expected. So now I'm
curious.



Dirk Goldgar said:
In
wdsnews said:
I typed a SQL query into Access using "Where [Home Phone] Is Not
Null". When I next looked at it, the query said - Where [Home
Phone]<>"False". My intention is to move the SQL statement into my
VBA code.
What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and I'd
prefer to use the "Not Null".
Click to expand...

That's odd, and I can't reproduce it with a simple query. What exactly
did you do, and what is the data type of the [Home Phone] field? And what
version of Access are you using?

If [Home Phone] is a boolean (Yes/No) field in a table in an Access (Jet)
database, then it can *never* be Null as stored in the table. Boolean
fields are stored in a way that doesn't allow Nulls. The only way a
boolean field in a query can be Null is in an outer join, where the field
comes from a table that had no matching records.

So maybe Access is trying to fix up your query to "do what you meant"
somehow; however, as I said, I couldn't reproduce it. I'm using Access
2002, so maybe in a later version Access is being more "helpful".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Thank you for confirming my understanding that "Is Not Null" is the correct
syntax, and '<>False' would give a different result. I'm a brand new
convert to Access and your reply is very helpful. Thank you.


Douglas J. Steele said:
They definitely aren't the same thing.

Is Not Null is the correct syntax: the second syntax would look for the
literal string "False" in the telephone number field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


wdsnews said:
I typed a SQL query into Access using "Where [Home Phone] Is Not Null".
When I next looked at it, the query said - Where [Home Phone]<>"False".
My intention is to move the SQL statement into my VBA code.

What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and I'd prefer to
use the "Not Null".
Click to expand...
Click to expand...
 
Thank you. I was sure I hadn't mistyped. I found two "Name AutoCorrect"s
to uncheck in Options.

Track name autoCorrect info
Perform name AutoCorrect

Those were checked by default and deserve to be called "Name AutoCorrupt".



Bob Quintal said:
Thank you for your reply. I'm glad to know this is not
normal. I'm using Access 2003, and it's possible that I
mistyped something that caused Access to replace my 'Where'
statement with its own? hmmm. Perhaps wrong placement or
number of parenthesis? I don't know. [Home Phone] is a text
field.

Now I've gone back in to the query and retyped "Is Not Null"
to replace '<>False'. This time Access kept it and all looks
as expected. So now I'm curious.
Click to expand...

Menu to Tools->Options->general and make sure that the
checkboxes in Name AutoCorrect are NOT checked. It sometimes
does things like this, and a lot worse: the feature is
deservedly referred to as Name AutoCorrupt by several posters
here

Dirk Goldgar said:
In I typed a SQL query into Access using "Where [Home Phone] Is
Not Null". When I next looked at it, the query said - Where
[Home Phone]<>"False". My intention is to move the SQL
statement into my VBA code.
What's the significance?
Does it matter which version of the statement I use?

To me, "Not False" is a different thing from "Not Null" and
I'd prefer to use the "Not Null".

That's odd, and I can't reproduce it with a simple query.
What exactly did you do, and what is the data type of the
[Home Phone] field? And what version of Access are you
using?

If [Home Phone] is a boolean (Yes/No) field in a table in an
Access (Jet) database, then it can *never* be Null as stored
in the table. Boolean fields are stored in a way that doesn't
allow Nulls. The only way a boolean field in a query can be
Null is in an outer join, where the field comes from a table
that had no matching records.

So maybe Access is trying to fix up your query to "do what
you meant" somehow; however, as I said, I couldn't reproduce
it. I'm using Access 2002, so maybe in a later version
Access is being more "helpful".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top