BUG in Not Like query

[H. Williams]

There appears to be a bug in Microsoft Access when you create queries with
"Not Like" logic. The queries will not display any records where the
corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not contain abc.
Instead it displays every record where the notes does not contain abc and
the notes fields is not null. In other words, all records with blank notes
fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR [Notes] Is
Null";

Still this is hard to explain to most users.

 

[Douglas J. Steele]

It's not a bug. Null cannot be checked for equality or inequaltity: it's
simply Null.

Blank and Null aren't the same.

 

[H. Williams]

How can a field be blank and not have a null value?

It's not a bug. Null cannot be checked for equality or inequaltity: it's
simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There appears to be a bug in Microsoft Access when you create queries
with "Not Like" logic. The queries will not display any records where
the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not contain
abc. Instead it displays every record where the notes does not contain
abc and the notes fields is not null. In other words, all records with
blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR [Notes]
Is Null";

Still this is hard to explain to most users.

 

[Douglas J. Steele]

In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table, you
can specify that zero length strings are allowed. This is the difference
between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How can a field be blank and not have a null value?

It's not a bug. Null cannot be checked for equality or inequaltity: it's
simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There appears to be a bug in Microsoft Access when you create queries
with "Not Like" logic. The queries will not display any records where
the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not contain
abc. Instead it displays every record where the notes does not contain
abc and the notes fields is not null. In other words, all records with
blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR [Notes]
Is Null";

Still this is hard to explain to most users.

 

[H. Williams]

I'm afraid I'm missing something. It is my understanding that a text field
in an Access table can only have 2 possible values:
1. Null
2. A string (including zero length strings)

If a text field doesn't have a default value, wont it be assigned Null when
the record is created? Isn't that the same as Blank? When you create
queries you cab filter by Is Null or ="". How would you filter for Blank?
Thanks for your replies.

In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table, you
can specify that zero length strings are allowed. This is the difference
between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How can a field be blank and not have a null value?

It's not a bug. Null cannot be checked for equality or inequaltity: it's
simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There appears to be a bug in Microsoft Access when you create queries
with "Not Like" logic. The queries will not display any records where
the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not contain
abc. Instead it displays every record where the notes does not contain
abc and the notes fields is not null. In other words, all records with
blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR [Notes]
Is Null";

Still this is hard to explain to most users.

 

[Douglas J. Steele]

There are 2 properties that you need to look at when you create a Text
field: the Required property, and the Allow Zero Length property.

Check out
http://msdn.microsoft.com/library/en-us/vbaac11/html/acproAllowZeroLength_HV05187003.asp
for details on how these two properties work together.

To check for a blank, you'd use = " "

However, Access normally won't save a blank if you try to enter it through
the GUI. I believe, however, that you can write a SQL query that can insert
a blank into a field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm afraid I'm missing something. It is my understanding that a text
field in an Access table can only have 2 possible values:
1. Null
2. A string (including zero length strings)

If a text field doesn't have a default value, wont it be assigned Null
when the record is created? Isn't that the same as Blank? When you
create queries you cab filter by Is Null or ="". How would you filter for
Blank?
Thanks for your replies.

In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table,
you can specify that zero length strings are allowed. This is the
difference between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How can a field be blank and not have a null value?

It's not a bug. Null cannot be checked for equality or inequaltity:
it's simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There appears to be a bug in Microsoft Access when you create queries
with "Not Like" logic. The queries will not display any records where
the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not
contain abc. Instead it displays every record where the notes does not
contain abc and the notes fields is not null. In other words, all
records with blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR
[Notes] Is Null";

Still this is hard to explain to most users.

 

[H. Williams]

I'm not trying to give you a hard time, but I really think you are mistaken.
As I stated there can only be 2 possible values for a text field:
1. Null (assuming Required is false)
OR
2. A string (including zero length strings if Allow Zero Length = true)

There is no 3rd Microsoft definition for blank. Blank could mean either
Null or a zero length string. To check for both cases you would need: Is
Null OR ="" (with no space).

My only point is that NOT LIKE logic skips NULL values (It does not skip
zero length strings). Microsoft should add a caveat to documentation.
Otherwise query results using Not Like could be very misleading. Even
better, Access could be updated so a Not Like query could return Null
values, because obviously a null value is Not Like any possible string
criteria.
Thanks again for your time.

There are 2 properties that you need to look at when you create a Text
field: the Required property, and the Allow Zero Length property.

Check out
http://msdn.microsoft.com/library/en-us/vbaac11/html/acproAllowZeroLength_HV05187003.asp
for details on how these two properties work together.

To check for a blank, you'd use = " "

However, Access normally won't save a blank if you try to enter it through
the GUI. I believe, however, that you can write a SQL query that can
insert a blank into a field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm afraid I'm missing something. It is my understanding that a text
field in an Access table can only have 2 possible values:
1. Null
2. A string (including zero length strings)

If a text field doesn't have a default value, wont it be assigned Null
when the record is created? Isn't that the same as Blank? When you
create queries you cab filter by Is Null or ="". How would you filter
for Blank?
Thanks for your replies.

In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table,
you can specify that zero length strings are allowed. This is the
difference between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



How can a field be blank and not have a null value?

message It's not a bug. Null cannot be checked for equality or inequaltity:
it's simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There appears to be a bug in Microsoft Access when you create queries
with "Not Like" logic. The queries will not display any records
where the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not
contain abc. Instead it displays every record where the notes does
not contain abc and the notes fields is not null. In other words,
all records with blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR
[Notes] Is Null";

Still this is hard to explain to most users.

 

[Douglas J. Steele]

Actually, I'm not mistaken.

Try an Insert query like:

INSERT INTO MyTable(ID, MyTextField)
VALUES (1, " ")

MyTextField will show up as being 1 character long.

Now, on many versions of Access (sorry, I don't have them all installed on
this machine, so I can't tell you which versions...), using WHERE
MyTextField = " " will also retrieve any rows with a zero-length string.

I don't disagree with you that the documentation should be a little more
explicit about how Null values are treated, but I for one would not want the
default behaviour changed: as I said earlier, to me Null is a very different
thing than either blank or a ZLS, and I would not want it treated the same.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not trying to give you a hard time, but I really think you are
mistaken. As I stated there can only be 2 possible values for a text
field:
1. Null (assuming Required is false)
OR
2. A string (including zero length strings if Allow Zero Length = true)

There is no 3rd Microsoft definition for blank. Blank could mean either
Null or a zero length string. To check for both cases you would need: Is
Null OR ="" (with no space).

My only point is that NOT LIKE logic skips NULL values (It does not skip
zero length strings). Microsoft should add a caveat to documentation.
Otherwise query results using Not Like could be very misleading. Even
better, Access could be updated so a Not Like query could return Null
values, because obviously a null value is Not Like any possible string
criteria.
Thanks again for your time.

There are 2 properties that you need to look at when you create a Text
field: the Required property, and the Allow Zero Length property.

Check out
http://msdn.microsoft.com/library/en-us/vbaac11/html/acproAllowZeroLength_HV05187003.asp
for details on how these two properties work together.

To check for a blank, you'd use = " "

However, Access normally won't save a blank if you try to enter it
through the GUI. I believe, however, that you can write a SQL query that
can insert a blank into a field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm afraid I'm missing something. It is my understanding that a text
field in an Access table can only have 2 possible values:
1. Null
2. A string (including zero length strings)

If a text field doesn't have a default value, wont it be assigned Null
when the record is created? Isn't that the same as Blank? When you
create queries you cab filter by Is Null or ="". How would you filter
for Blank?
Thanks for your replies.


In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table,
you can specify that zero length strings are allowed. This is the
difference between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



How can a field be blank and not have a null value?

message It's not a bug. Null cannot be checked for equality or inequaltity:
it's simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There appears to be a bug in Microsoft Access when you create
queries with "Not Like" logic. The queries will not display any
records where the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not
contain abc. Instead it displays every record where the notes does
not contain abc and the notes fields is not null. In other words,
all records with blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR
[Notes] Is Null";

Still this is hard to explain to most users.

 

[H. Williams]

Your insert query creates a field with a string value equal to a single
space (chr$(32))
What's strange is that Access 2003 can't distinguish a zero length string
from a string of spaces. I ran your insert query 3 times with "", " " and "
".
I then tried Select MyTextField from MyTable Where MyTextField="". It
pulled up all three records. I then changed the where to
MyTextField=chr(32) and it still pulled up all 3 records. I also tried
changing the where to MyTextField=" " and no matter how many
spaces, it still returns ALL the records with zero length strings or spaces.
Access treats any number of spaces as a zero length string as long as the
field only contains spaces or no spaces ("").

Actually, I'm not mistaken.

Try an Insert query like:

INSERT INTO MyTable(ID, MyTextField)
VALUES (1, " ")

MyTextField will show up as being 1 character long.

Now, on many versions of Access (sorry, I don't have them all installed on
this machine, so I can't tell you which versions...), using WHERE
MyTextField = " " will also retrieve any rows with a zero-length string.

I don't disagree with you that the documentation should be a little more
explicit about how Null values are treated, but I for one would not want
the default behaviour changed: as I said earlier, to me Null is a very
different thing than either blank or a ZLS, and I would not want it
treated the same.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not trying to give you a hard time, but I really think you are
mistaken. As I stated there can only be 2 possible values for a text
field:
1. Null (assuming Required is false)
OR
2. A string (including zero length strings if Allow Zero Length = true)

There is no 3rd Microsoft definition for blank. Blank could mean either
Null or a zero length string. To check for both cases you would need:
Is Null OR ="" (with no space).

My only point is that NOT LIKE logic skips NULL values (It does not skip
zero length strings). Microsoft should add a caveat to documentation.
Otherwise query results using Not Like could be very misleading. Even
better, Access could be updated so a Not Like query could return Null
values, because obviously a null value is Not Like any possible string
criteria.
Thanks again for your time.

There are 2 properties that you need to look at when you create a Text
field: the Required property, and the Allow Zero Length property.

Check out
http://msdn.microsoft.com/library/en-us/vbaac11/html/acproAllowZeroLength_HV05187003.asp
for details on how these two properties work together.

To check for a blank, you'd use = " "

However, Access normally won't save a blank if you try to enter it
through the GUI. I believe, however, that you can write a SQL query that
can insert a blank into a field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm afraid I'm missing something. It is my understanding that a text
field in an Access table can only have 2 possible values:
1. Null
2. A string (including zero length strings)

If a text field doesn't have a default value, wont it be assigned Null
when the record is created? Isn't that the same as Blank? When you
create queries you cab filter by Is Null or ="". How would you filter
for Blank?
Thanks for your replies.


message In essence, Null means "Don't Know", whereas blank means there isn't a
value. While similar, there is a subtle difference.

To further complicate things, when you create a text field in a table,
you can specify that zero length strings are allowed. This is the
difference between "" and " ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



How can a field be blank and not have a null value?

message It's not a bug. Null cannot be checked for equality or inequaltity:
it's simply Null.

Blank and Null aren't the same.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There appears to be a bug in Microsoft Access when you create
queries with "Not Like" logic. The queries will not display any
records where the corresponding field is blank.
For example:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc*";
This should display every record where the notes field does not
contain abc. Instead it displays every record where the notes does
not contain abc and the notes fields is not null. In other words,
all records with blank notes fields gets improperly excluded.

I know this can be fixed by changing the query to:
Select [Notes] From [Contacts] Where [Notes] Not Like "*abc* OR
[Notes] Is Null";

Still this is hard to explain to most users.