Calculate Age With DOB Validation

[Lynn_AccessUser]

I have a function which calculates the age based on the DOB. I am trying to add data validation.

1. I tried adding <=Now() to the property data validation rule but if a valide DOB is entered and the user tries to go back and delete the DOB it won't let them . . . the message box with the validation text will show.

2. I tried adding the following code to the Before Update event which works on all the other date fields . . . of course those fields do not call a function to calculate anything. The code doesn't work at all, it still allows a user to enter in a DOB > Now().

If varDOB > Now() Then
MsgBox "You can not enter a date greater than today's date."
End If

3. I tried adding the validation to the function itself. Problem is that when you tab out of the field the entire form turns white and the msgbox appears. You then have to hit OK 2x to close the msgbox and the cursor goes to the field that shows the calculated age instead of the DOB field. Here is the code:

Function Age(varDOB As Variant, Optional ByVal varAsOfDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varDOB) Then Age = 0: Exit Function

If varDOB > Now() Then
MsgBox "You can not enter a date greater than today's date.": Exit Function
End If

If Not IsDate(varAsOfDate) Then
varAsOfDate = Date
End If

varAge = DateDiff("yyyy", varDOB, varAsOfDate)
If Date < DateSerial(Year(Now), Month(varDOB), _
Day(varDOB)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

 

[Lynn_AccessUser]

I got it . . .

Property Data Validation:

Is Null OR <=Now()

I had tried this earlier but didn't notice that I kept typing IsNull instead of Is Null. IsNull is Crystal reports syntax . . . oops.