By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Null Value

P: n/a

Hi,

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
work

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"!

Aug 31 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>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
work

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"!
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.
Similarly,
x = null
is never true, it's null. The correct test is
x is null

You probably want to write your test:
If PBV_rstCompany.Fields("Installer") <> "Y"

or PBV_rstCompany.Fields("INstaller") is null
Then

Gordon L. Burditt
Aug 31 '05 #2

P: n/a
Joel wrote:
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
work


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.

Regards,
Bill K.
Aug 31 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.