H
Homer
I'm quite new to all this but does anyone have any idea why this isn't
working?
SELECT *
FROM COMBINED
WHERE F11="";
Thanks,
PAR
working?
SELECT *
FROM COMBINED
WHERE F11="";
Thanks,
PAR
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Spencer
Probably because F11 does not contain a zero length string but is NULL. Try
WHERE F11 Is Null
in place of
F11 = ""
If that doesn't work try
WHERE Trim(F11 & "") = ""
Or even the following which handles fields from databases that fill certain
datatypes to the defined length
WHERE Len(Trim(F11 & "")) = 0
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
WHERE F11 Is Null
in place of
F11 = ""
If that doesn't work try
WHERE Trim(F11 & "") = ""
Or even the following which handles fields from databases that fill certain
datatypes to the defined length
WHERE Len(Trim(F11 & "")) = 0
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
M
Michel Walsh
An empty string is a string with no character in it. A zero is a value that
says there is no quantity. A NULL is a kind of meta-data, it stands for
"there is no data yet, we don't know, or it is not applicable to have data".
As example, how many children have you gave birth, for a woman, 0 means we
are sure she did not gave birth, while null means we don't know how many
children she may have got (0, 1, 2, ... we just don't know), and null, for a
man, is probably more applicable than 0. That was about difference between
NULL and 0, but it is a little bit the same between a string with no
character, "", and NULL.
SELECT *
FROM combined
WHERE f11 IS NULL
would pick records where f11 has a null value in it. WE DO NOT USE:
WHERE f11=NULL
because it is 'insane' to compare a null with another null: Does my hair
color matches the color of my car? You don't know me, so, for you, my hair
color = null, and my car color = null. So, does null = null ? if you say
yes, you say that my hair color matches my car color! if you say no, you say
the don't! how can you be sure? you don't know, and don't know value is ...
null. So, to null = null, the answer is "don't know" (or null), not "yes"
(or true). That is why we use IS, not =, when comparing to null. See NULL
as a friend, a little bit weird, well, as weird the introduction of the 0
must have appear when it was introduced, but a good friend, when we learn to
know it.
Hoping it may help,
Vanderghast, Access MVP
says there is no quantity. A NULL is a kind of meta-data, it stands for
"there is no data yet, we don't know, or it is not applicable to have data".
As example, how many children have you gave birth, for a woman, 0 means we
are sure she did not gave birth, while null means we don't know how many
children she may have got (0, 1, 2, ... we just don't know), and null, for a
man, is probably more applicable than 0. That was about difference between
NULL and 0, but it is a little bit the same between a string with no
character, "", and NULL.
SELECT *
FROM combined
WHERE f11 IS NULL
would pick records where f11 has a null value in it. WE DO NOT USE:
WHERE f11=NULL
because it is 'insane' to compare a null with another null: Does my hair
color matches the color of my car? You don't know me, so, for you, my hair
color = null, and my car color = null. So, does null = null ? if you say
yes, you say that my hair color matches my car color! if you say no, you say
the don't! how can you be sure? you don't know, and don't know value is ...
null. So, to null = null, the answer is "don't know" (or null), not "yes"
(or true). That is why we use IS, not =, when comparing to null. See NULL
as a friend, a little bit weird, well, as weird the introduction of the 0
must have appear when it was introduced, but a good friend, when we learn to
know it.
Hoping it may help,
Vanderghast, Access MVP