Coping with empty field

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

Tony Williams

I have a control that calculates the age here is the code
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

However sometimes I don't know the Date of Birth (Date_of_Birth) and when I
tab to Age I get an error message that says Runtime error 94 Illegal use of
null. So I suspect I need some code that says if the Date of Birth is empty
ignore and age is 0 or also empty if date of birth is present then carry on
with the calculation. I've tried something like
If isNull(Date_of_Birth.Value) Then
datebirthday = "" Else
But this gives me error message and I don't think it's that simple
Can anyone help please?
TIA
Tony
 
Tony,
if the Date_of_Birth text box is set as a string or
general then you are passing a string to a date variable.
and "" is not null, its empty. Below is a possible
solution.

royc
Digital Connections Inc.
www.dcisite.com
ex ----------------------------------
date_of_birth textbox is set up as date type
-----------------------------------------------------
Private Sub txtAge_Enter()
on error goto err_condition

if isnull(date_of_birth) then
txtage.value = 0
else
txtAge.Value = date - date_of_birth
If txtAge.Value < 18 Then
MsgBox "This person is under 18!", bOKOnly, "Warning"
End If
endif

err_condition:
if err.number<>0 then
txtage.value = 0
msgbox "Age error: " & err.number & ": " &
err.description
endif
End Sub
 
Thanks royc work fine!
Tony
royc said:
Tony,
if the Date_of_Birth text box is set as a string or
general then you are passing a string to a date variable.
and "" is not null, its empty. Below is a possible
solution.

royc
Digital Connections Inc.
www.dcisite.com
ex ----------------------------------
date_of_birth textbox is set up as date type
-----------------------------------------------------
Private Sub txtAge_Enter()
on error goto err_condition

if isnull(date_of_birth) then
txtage.value = 0
else
txtAge.Value = date - date_of_birth
If txtAge.Value < 18 Then
MsgBox "This person is under 18!", bOKOnly, "Warning"
End If
endif

err_condition:
if err.number<>0 then
txtage.value = 0
msgbox "Age error: " & err.number & ": " &
err.description
endif
End Sub
Click to expand...
 
Back
Top