VBA 2003: If Null...

[Guest]

What is the proper method of matching to a NULL value in VBA code? 'Is Null'?
'=Null'? Other?

I have a form through which users add data that gets apprended to a table
via a SQL string. This SQL string fails via End...debug error if any of the
fields are left blank. Instead of simply failing each time, returning an
error regardless of which null value it failed on, I am setting up a series
of If statements which check each of the fields (6 total) for a lack of
information, then return a message indicating which one(s) it failed on, and
changing the .backcolor of the fields to red.

If I leave a field blank, (Me.Analyst for example) I get different results
depending on how I set up the if statement

If I try:
"If Me.Analyst = Null Then"
the If returns FALSE, ignoring the MsgBox code, even though when I add a
breakpoint and take a look at the value, the field value shows as 'Null'. The
code continues on and errors on the SQL statement.

If I try:
"If Me.Analyst Is Null Then"
the If statement generates an error 'invalid object'.

I can't bind this to a table given the circumstances, and since 4 of the 6
values are text strings of whatever the users type in, there is nothing I can
clearly match on other than 'is null'. The fields begin completely blank, not
assigned any value.

Thanks.

 

[Dirk Goldgar]

In

What is the proper method of matching to a NULL value in VBA code?
'Is Null'? '=Null'? Other?

I have a form through which users add data that gets apprended to a
table via a SQL string. This SQL string fails via End...debug error
if any of the fields are left blank. Instead of simply failing each
time, returning an error regardless of which null value it failed on,
I am setting up a series of If statements which check each of the
fields (6 total) for a lack of information, then return a message
indicating which one(s) it failed on, and changing the .backcolor of
the fields to red.

If I leave a field blank, (Me.Analyst for example) I get different
results depending on how I set up the if statement

If I try:
"If Me.Analyst = Null Then"
the If returns FALSE, ignoring the MsgBox code, even though when I
add a breakpoint and take a look at the value, the field value shows
as 'Null'. The code continues on and errors on the SQL statement.

If I try:
"If Me.Analyst Is Null Then"
the If statement generates an error 'invalid object'.

I can't bind this to a table given the circumstances, and since 4 of
the 6 values are text strings of whatever the users type in, there is
nothing I can clearly match on other than 'is null'. The fields begin
completely blank, not assigned any value.

In VBA code, you have to use the IsNull function; e.g.,

If IsNull(Me.Analyst) Then

 

[Guest]

You can use the ISNULL( ) function to test to see if it is null, but that
will not tell you if it is a zero length string.

If the default value for a field is NULL then when you go to a new record,
it should be NULL, but if you enter a value in the field, and then go back
and just delete the contents, you are left with a zero length string. When I
want to test for ISNULL or is zero length string (""), I use the test:

IF LEN(me.txtControl & "") = 0

The LEN( ) function will generate an error if the control value is NULL, but
by concatenating a zero length string to it, it will indicate that the length
is zero for either condition.

HTH
Dale

 

[Dirk Goldgar]

In

If the default value for a field is NULL then when you go to a new
record, it should be NULL, but if you enter a value in the field, and
then go back and just delete the contents, you are left with a zero
length string.

This is not normally the case. Normally, if your text box is bound to a
text field, deleting the contents of the text box will cause a Null
value to be stored in the field. I'm not sure if this is true in a memo
field or not, but you can test it easily enough. To store a zero-length
string in a field via a text box, you usually have to enter "" in the
text box -- there appears to be special code in the Access.Textbox
control to interpret that entry.

Of course, there are other ways to get a zero-length string into a
field; for example, by importing data, or by assigning the value in
code. So your suggestion for a common test using

IF LEN(me.txtControl & "") = 0

.... is still a good one if you want to catch both cases.

 

[Dale Fye]

Dirk,

Hadn't thought about that (memo vs. text). I use a lot of memo fields and I
probably encountered the problem and solution years ago when dealing with a
memo field. Thanks for the feedback.

Dale