Query Results From DateDiff return wrong age

[Lisa_King_3]

Hi people, i am trying to perform a query that searchs a table of members for people between a minimum age and maximum age (the age is calculated by their date of birth and the current date)

Problem: The query performs fine except the resulting age is wrong, it is always 1 year more than it should be, e.g. a member is only 17, and yet the result of the query shows them to be 18.

The field name I have used is :
Expr1: DateDiff("yyyy",[DateOfBirth],Now())

And the criteria is:
Between [Enter Minimum Age] And [Enter Maximum Age]

Can anyone shine any light on how to alter the filed formula so that it correctly works out members age depending on their date of birth and the current date?

Lisa x

 

[bat17]

I dont think that you can get age properly with a simple function. Here is some code from the Chip Pearson web site that you could put into a module and the use as a custom function

Function Age(Date1 As Date, Date2 As Date) As String
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
    M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
    End If
    Age = Y & " years " & M & " months " & D & " days"
End Function

HTH

Peter

 

[Lisa_King_3]

but how do i put that in a microsoft query?

 

[bat17]

Sorry, I did not look at the code properly and it returns the age as a string rather than a number! The following function should just return the age in your query.

exp1:Year(Now()) - Year([DateOfBirth]) + (DateSerial(Year(Now()), Month([DateOfBirth]), Day([DateOfBirth])) > Now())

Peter

 

[Lisa_King_3]

cheers, that one works