Re: Proper syntax for Is Null is Select Case
From: Bill Mitchell (BillMitchell_at_discussions.microsoft.com)
Date: 10/05/04
- Next message: AlCamp: "Re: Scrolling through a drop down box"
- Previous message: Albert D. Kallal: "Re: Proper syntax for Is Null is Select Case"
- In reply to: Albert D. Kallal: "Re: Proper syntax for Is Null is Select Case"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 5 Oct 2004 16:15:02 -0700
Thanks Al,
I sorta figured as much and have thought about using the Nz(). For now,
I'll just use IsNull() in my If Statement.
I'm always amazed that you guys have the time for these detailed
explanations when you aren't getting paid anything (by me anyway).
Oh well, much thanks for your good work.
P.S., I'm a successful IT headhunter. Shoot me a resume and maybe I can get
you some contracting work from time to time :o)
mitchell@executivedecision.biz
Thanks again.
"Albert D. Kallal" wrote:
> 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).
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.attcanada.net/~kallal.msn
>
>
>
- Next message: AlCamp: "Re: Scrolling through a drop down box"
- Previous message: Albert D. Kallal: "Re: Proper syntax for Is Null is Select Case"
- In reply to: Albert D. Kallal: "Re: Proper syntax for Is Null is Select Case"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
