Null Value

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


I added a field to my company table (PBV_rstCompany.Fields("Installer")) the
default value of the field is Null. I place this If statement and it doesn't

    If PBV_rstCompany.Fields("Installer") <> "Y" Then
        txtInstaller.Visible = False
        lblInstallerLBL.Visible = False
        cmdNextInstaller.Visible = False
        cmdPrevInstaller.Visible = False
    End If

It assumes the statement is false when in fact it's true... Null in not
equal "Y"!

Re: Null Value

Joel wrote:
Quoted text here. Click to load it

Almost any expression involving a NULL yields a NULL as the value of the
expression.  A NULL expression in a boolean condition such as your IF
works like FALSE for purposes of satisfying the condition.

In SQL, NULL is a state, not a value.  You cannot compare NULL to
anything -- it will always yield NULL.  The idea is that NULL represents
the absence of a value, or an unknown value, or a value that has not
been set yet.  E.g., is my middle name "Kevin"?  We cannot say for
sure--no value has been entered in the middle_name field yet.

If you must have a non-null state in your "Installer" field, then you
should delare it as a NOT NULL field in your schema, and perhaps also
give it a DEFAULT value.  This is appropriate if you have a field that
_must_ be either "Y" or "N", and using NULL to indicate an absence of a
value is meaningless for that field.

Otherwise you must handle the case of a NULL state differently than
handling value comparisons.  SQL provides a predicate IS [NOT] NULL,
which returns true or false.

Bill K.

Re: Null Value

Quoted text here. Click to load it

Get used to SQL 3-valued logic.  The result of:
    PBV_rstCompany.Fields("Installer") <> "Y"
when the left side is null has the value null, not false or true.
    x = null
is never true, it's null.  The correct test is
    x is null

You probably want to write your test:

Quoted text here. Click to load it
    or PBV_rstCompany.Fields("INstaller") is null

                    Gordon L. Burditt

Site Timeline