You can't test for
is <some value> Is Nulll
that above is not legal (except when using sql, but that is a different
matter). So, don't get confused with the sql syntax, and the VB coding
syntax.
In most flavors of sql (from sql server, to MySql, to JET), you can go:
select * from tblCustomers where City is Null
So,the "is null" deal belongs to sql..and not VB code....
In code, you would have to go:
if <someExpresson> = <Null> then
Since null is un-defined, then the above gives no useful information, and is
also "null", and thus still undefined.
The only way to test for a null value is to the "isnull()" function that
returns something useful (in this case, true, or false).
So, to test, and check for nulls in code, you use a function, and that
function returns a Boolean "true" or "false"
if isnull(me!LastName) = true then
msgbox "the last name has not been entered"
exit sub ' don't run rest of code...
end if
Hence, you can't use the select case construct, since select case assumes a
known value, AND THEN you give a list of conditions.
eg:
Select Case Me!City
Case "N.Y", "New York"
MsgBox "The city is New York"
Case "Edmonton","Ed"
MsgBox "The City is Edmonton"
Case Else
MsgBox "dont know the city"
End Select
The above actually does work for the null value, and the "case else" does
run if the expression is null. However, I would code in a special test like:
if isnull(Me!City) = true then
msgbox "City has not been entered"
' value is null....do whatebver.
exit sub
end if
You *can* also cast the null value into something else, like a string. You
use the nz() function to do this
nz(<possible null expression>, Value to substitute when null goes
here)
So, we can cast a null city to a empty string of "" like:
Select Case nz(Me!City,"")
Case "N.Y", "New York"
MsgBox "The city is New York"
Case "Edmonton"
MsgBox "The City is Edmonton"
Case ""
MsgBox "city has not been entered, and is ether null, or zero
length string"
End Select
So, in conclusion, you can't use a null value in a condition, you either use
isnull(), or cast it to another data type with nz()
Also, take note of
MsgBox "city has not been entered, and is ether null, or zero
length string"
It is VERY VERY VERY important as a developer to decide if you are going to
allow zero length strings, and ALSO nulls. If you do this, then you have to
code, and test for both possible situations.
If we start out with database, and the City field is NOT entered, then it is
null. If you enter a city, and then later the users erases the city, and you
allow zero length strings, then the city is no not null...but in fact a
empty string (""). If you been writing code, and designing your database
without any regards to nulls *and* zero length strings, then you may be
forced to re-write a lot of code here.
In my designs, I did not want to have to deal with
select * from tblCusomters where city is null
and also
select * from tblCustomers where city = ""
For me, as a developer, I want ONE thing that means a empty city field. So,
my coding standard is that ALL FIELDS that are empty are NULL.
So, make sure when you start developing, that you choose a standard, and
choose what, when and how you will test for a empty control, or empty fields
in a database. Some applications needs to know if a field has been ever
entered, but it is generally quite rare. So, it is VERY important to get
this right...other wise a lot of code will seem very "buggy" as some don't
work when you got nulls, or "" (zero length) values stored. If you code
don't know which kind of values to expect, then your code will have trouble
dealing with this issue. So, BEFORE you write code and start developing the
system, you need to choose a standard, and if you will, or will not allow
zero length strings (and, then you can write your code to deal with the
expected data based on your design decision).
)