Invalid use of Null error

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a control on a form that calculates a persons age from the date of
birth. However if the date of birth control is blank and the user tabs into
the age control they get a Invalid use of Null error. How can I stop the
error message?
Here is my code for age

Private Sub txtAge_Enter()
Dim dateBirthday As Date
dateBirthday = Date_of_Birth.Value
Dim varAge As Variant
varAge = -DateDiff("yyyy", Date, dateBirthday)
Dim varDiff As Variant
varDiff = DateDiff("d", DateAdd("yyyy", varAge, dateBirthday), Date)
If varDiff < 0 Then
varAge = varAge - 1
End If
txtAge.Value = varAge
If txtAge.Value < 18 Then
MsgBox "This person is under 18!", vbOKOnly, "Warning"
End If
End Sub


Would appreciate any help
Thanks
Tony
 
hi Tony,

Tony said:
I have a control on a form that calculates a persons age from the date of
birth. However if the date of birth control is blank and the user tabs into
the age control they get a Invalid use of Null error. How can I stop the
error message?

Dim dateBirthday As Date
dateBirthday = Date_of_Birth.Value
Click to expand...
The error occurs in the line above, cause normal value typed variables
cannot hold Null.
Dim varAge As Variant
varAge = -DateDiff("yyyy", Date, dateBirthday)
Click to expand...
Only Variants can be Null.

Change your sub like that:

Private Sub txtAge_Enter()

Dim dateBirthday As Date
Dim varAge As Variant
Dim varDiff As Variant

varAge = 0

If Not IsNull(Date_of_Birth.Value) Then
dateBirthday = Date_of_Birth.Value
varAge = -DateDiff("yyyy", Date, dateBirthday)
varDiff = DateDiff("d", _
DateAdd("yyyy", varAge, dateBirthday), _
Date)
If varDiff < 0 Then
varAge = varAge - 1
End If
End If

txtAge.Value = varAge
If txtAge.Value < 18 Then
MsgBox "This person is under 18!", vbOKOnly, "Warning"
End If

End Sub


mfG
--> stefan <--
 
Back
Top