good morning,
I have a function that calculates the difference between two dates. the output is a string in the form "67 years, 229 days"
this obviously requires two dates as arguments (DoB, and DoR), and it is not possible for the second argument DoR to be anything other than a date. if DoR is not known there is no record in the database.
the first argument DoB can be a null. so my function checks for Nulls, or at least it is supposed to but I can't get it to work properly.
I have checked using the vartype() function and where DoB is not known vartype() returns 1, indicating a null. so this is not zls type scenario.
however, where both dates are known, the return is correct, but when DoB is not known the function returns #Error.
the function looks like this:
Code:
Public Function Age_m(DoB As Date, DoR As Date) As String
' this returns the athlete's age as a text string, nn years, nnn days '
Dim race As Date
Dim delta As Integer
Dim days As Integer
If IsNull(DoB) Then
Age_m = " - "
Else
race = DateSerial(Year(DoR), Month(DoB), Day(DoB))
delta = IIf(DoR - race > 0, Year(DoR) - Year(DoB), Year(DoR) - Year(DoB) - 1)
days = IIf(DoR - race > 0, DoR - race, 365 + DoR - race)
Age_m = Format(delta, "00") + " years, " + Format(days, "0") + " days"
End If
End Function
any clues as to how to improve this would be greatly appreciated.
thank you
Cottonshirt