Calculating age

[mattP]

Ok, I currently have a form (called frmreview) that is linked to a Query (called qryreview). On my form I have a field for date opened, one ofr date closed and a field for age of issue.

How can I set the form so that if there is no closed date the age is a running total of date opened to current date but if there is a closed date (or status is set to closed) then it a date difference between the two dates.

I have an idea of how this works but am lost on the code or whether I do this in the query or on the form itself.

any help would be greatly appreciated.

mattP

 

[Mile-O]

Put this in a module:

Public Function CALCULATEAGE(ByVal dteDOB As Date, Optional SpecDate As Variant) As Integer
    
    On Error GoTo Err_CalculateAge
    
    Dim dteBase As Date
    Dim intCurrent As Date
    Dim intEstAge As Integer
    
    ' Determine if SpecDate parameter has been passed
    If IsMissing(SpecDate) Then
        ' If not, use current date
        dteBase = Date
    Else
        ' Otherwise use the SpecDate parameter
        dteBase = SpecDate
    End If
    
    ' Create an estimated age by getting the number of years
    ' between the date of birth and the secondary date
    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    ' Create the exact age from evaluating the month and day based
    ' on the year
    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    CALCULATEAGE = intEstAge + (dteBase < intCurrent)
    
    Exit Function
    
Err_CalculateAge:
    CALCULATEAGE = -1
    
End Function

Lock and disable your textbox. Put the following as its ControlSource, substituting the names for the correct controls.

=CalculateAge([txtDOB], Nz([txtDateClosed,Date()))

 

[mattP]

SJ Mc Abney,

Thanks for the quick repsonse, I'll have a go with this code and see how it goes.

Many thanks

MattP

 

[mattP]

Ok whilst looking into the coding etc.. I tried this bit of code on the form itself,

If Me.Status = "Closed" Then
Me.txtAge = DateDiff("d", Me.txtDate_Opened, Me.txtClosed)
Else
Me.txtAge = DateDiff("d", Me.txtDate_Opened, Date)

End If

I put this in as a sub routine and called it for the "On Current" event for the form and the "After Update" Event for date opened, date closed and the Status.
Everything seems to work fine but many thanks for your advice, the people and pages on this forum have provided a wealth of information to me over the past year.

Thanks

MattP