When Null isn't Null

[Guest]

I am using a VB procedure to ensure that all the fields in a form are
populated before a save/close action is allowed. I'm using code like this:

If IsNull([First Name]) Then _
MissingFields = MissingFields & CRLF & "First Name"
If IsNull([Street Address]) Then _
MissingFields = MissingFields & CRLF & "Street Address"

CRLF is a variable containing Chr(10) and Chr(13). The MissingFields
variable is used in an error message box identifying which fields need to be
populated.

When the form is in Data Entry mode and a field is left blank, it works
fine. When the form is in Edit mode and the value in a previously populated
field is deleted, the IsNull() function does not recognize the field as null.
Why is this? Is there another function I can use which will work?

Chris

 

[Marshall Barton]

I am using a VB procedure to ensure that all the fields in a form are
populated before a save/close action is allowed. I'm using code like this:

If IsNull([First Name]) Then _
MissingFields = MissingFields & CRLF & "First Name"
If IsNull([Street Address]) Then _
MissingFields = MissingFields & CRLF & "Street Address"

CRLF is a variable containing Chr(10) and Chr(13). The MissingFields
variable is used in an error message box identifying which fields need to be
populated.

When the form is in Data Entry mode and a field is left blank, it works
fine. When the form is in Edit mode and the value in a previously populated
field is deleted, the IsNull() function does not recognize the field as null.
Why is this? Is there another function I can use which will work?

Unless you've done something, the normal behavior when a
control 's value is cleared is to set it to Null.

Have you been able to determine what hte control's value is?

Maybe the table field's AllowZeroLength is set to Yes and
the value is "". If so, you can check for both conditions
by using:
If Nz([First Name], "") = "" Then _

 

[Guest]

Yes, and one additional note. Your CRLF variable is not necessary. There
are two intrinsic contstants which will do the same thing:
vbCrLf or vbNewLine

I am using a VB procedure to ensure that all the fields in a form are
populated before a save/close action is allowed. I'm using code like this:

If IsNull([First Name]) Then _
MissingFields = MissingFields & CRLF & "First Name"
If IsNull([Street Address]) Then _
MissingFields = MissingFields & CRLF & "Street Address"

CRLF is a variable containing Chr(10) and Chr(13). The MissingFields
variable is used in an error message box identifying which fields need to be
populated.

When the form is in Data Entry mode and a field is left blank, it works
fine. When the form is in Edit mode and the value in a previously populated
field is deleted, the IsNull() function does not recognize the field as null.
Why is this? Is there another function I can use which will work?

Unless you've done something, the normal behavior when a
control 's value is cleared is to set it to Null.

Have you been able to determine what hte control's value is?

Maybe the table field's AllowZeroLength is set to Yes and
the value is "". If so, you can check for both conditions
by using:
If Nz([First Name], "") = "" Then _

 

[Van T. Dinh]

I would use:

If Len(Trim([First Name] & "")) = 0 Then
MissingFields = MissingFields & CRLF & "First Name"
End If

If Len(Trim([Street Address] & "")) = 0 Then
MissingFields = MissingFields & CRLF & "Street Address"
End If

which will pick up both Null and white spaces.