[thelist] Testing for the various NULL's (.end thread)

Ken Schaefer ken at adOpenStatic.com
Thu Dec 5 17:20:01 CST 2002

From: "Rob Smith" <rob.smith at THERMON.com>
Subject: RE: [thelist] Testing for the various NULL's (.end thread)

: Actually you were right on the money Josh. However the syntax:
: >if manager.fields.item("Address2").value is not NULL then
: didn't work exactly; something about "Object Required". I think the
: TO and IS aren't fully supported. I know this is true for select...case
: statements. So I did some reformatting and came up with
: if not manager.fields.item("Address2").value = NULL
: But dangit! that's where I started from.

If you are doing this in VBScript, you need to use the IsNull() function

If not isNull(objRS.Fields.Item("myField").Value) then
    ' Field is not NULL
End If

If you are doing this in *SQL* then you use the syntax provided:

    myField Is Not NULL

(the Is Not Null syntax is not valid VBScript). The rest of the posts
explaining that NULLs do not equal anything else, including other NULLs is
very correct.

If you have a field where there is a possibility that it is both NULL, or a
zero length string, and you want to cut-down on the amount of VBScript
checking, then use the SQL COALESCE function in your stored procedure
(COALESCE is DB portable, as it is part of the ANSI SQL specification):

    COALESCE(myField, '') AS myField

will return myField if it is not NULL (even a ZLS), or a ZLS otherwise. In
your ASP page you can then do:

If objRS.Fields.Item("myField").Value = "" then
End If


More information about the thelist mailing list