When Null isn't Null

When Null isn't Null

Post by Q2hyaXMgQ » Thu, 06 Oct 2005 12:41:27


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
 
 
 

When Null isn't Null

Post by Marshall B » Thu, 06 Oct 2005 13:24:58


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 _

--
Marsh
MVP [MS Access]

 
 
 

When Null isn't Null

Post by S2xhdHV » Thu, 06 Oct 2005 21:50:02

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
 
 
 

When Null isn't Null

Post by Van T. Din » Thu, 06 Oct 2005 21:52:30

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.

--
HTH
Van T. Dinh
MVP (Access)