Invalid use of Null error

[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

 

[Stefan Hoffmann]

hi Tony,

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

The error occurs in the line above, cause normal value typed variables
cannot hold Null.

Dim varAge As Variant
varAge = -DateDiff("yyyy", Date, dateBirthday)

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

 

[Tony Williams]

Thanks Stefan worked just fine! Appreciate that.
Tony