Age not calculating correctly

[ImLost]

Hello -

I know there are a ton of threads about age, and I did read through several, but the most common answer isn't working. Everyone here has been extremely helpful in the past, so I'm hoping you can help me here. ^_^

I have two update queries to return a person's age at the beginning of a quarter and at the end of the quarter. The end query works just fine, but the beginning one isn't returning the correct values.

For the end query, I have:
DateDiff("yyyy",[DOB],Forms!ReportMenu!Text28)+Int(Format(Forms!ReportMenu!Text28,"mmdd")<Format([dob],"mmdd"))

(where text28 is the box on my form that contains the end date)

For the beginning query, I have:
DateDiff("yyyy",[DOB],Forms!ReportMenu!Text26)+Int(Format(Forms!ReportMenu!Text268,"mmdd")<Format([dob],"mmdd"))

(where text26 contains the begin date)

What caught my eye in particular is someone whose DOB is 01/08/91 with a begin date of 10/01/08 and an end date of 12/31/08. The end date is correctly recognizing him as 17, but the begin date is making him 16, when he turned 18 after the quarter ended.

Any ideas?

 

[Dennisk]

Here is the correct age calculation to use
Store it a a function then the code can be reused.

Public Function CalcAge(DOB) As Integer
    Dim CompareDate As Date
    CompareDate = Date
    If Not IsNull(DOB) Then
        CalcAge = Year(CompareDate) - Year(DOB) + (DateSerial(Year(CompareDate), Month(DOB), Day(DOB)) > CompareDate)
    End If
End Function

 

[ImLost]

Thanks for the quick response.

How would I enter that in design mode without using VBA? Sorry, I'm a noob when it comes to VBA and SQL.

 

[raskew]

Hi -

Dennisk beat me to it. Agree with that assessment. Suspect that the

Int(Format(Forms!ReportM enu!Text268,"mmdd")<Format([dob],"mmdd"))

portion is causing the problem. Here are a couple of examples that correctly return the age:

DOB = #01/08/91#
dteStart = #10/01/08#
? DateDiff("yyyy", DOB, dteStart) + (DateSerial(year(dteStart), month(DOB), day(DOB)) > dteStart)
 17 
 
DOB = #01/08/91#
dteEnd = #12/31/08#
? DateDiff("yyyy", DOB, dteEnd) + (DateSerial(year(dteEnd), month(DOB), day(DOB)) > dteEnd)
 17

Bob

 

[Mike375]

How would I enter that in design mode without using VBA? Sorry, I'm a noob when it comes to VBA and SQL.

Go to the Module section of the DB, it is an object like tables, queries, forms and macros.

Make a new module and copy and paste in what Denis has given you, then save it, it will default to Module1

This part....CalcAge..is the name of the function and is used in queries juts like any of the inbuilt Access functions like Right() etc

So in a query you make a calculated field like

YouNewFieldName:CalcAge([YourDateOfBirthField])

 

[raskew]

Since you'll not be calculating age as of the current date, you'll need to modify the function to allow the user to specify an end date. Example:

Function fAge2(DOB As Date, Optional dteEnd As Variant) As Integer
're: http://www.access-programmers.co.uk/forums/showthread.php?t=116432
'coded by: raskew
'Inputs:  1) ? fAge2(#4/13/53#, #10/23/06#)
'         2) ? fAge2(#11/1/53#, #10/23/06#)
'         3) ? fage2(#4/13/53#)
'Outputs: 1) 53
'         2) 52
'         3) 55

   dteEnd = IIf(IsMissing(dteEnd), Date, dteEnd)
   fAge2 = DateDiff("yyyy", DOB, dteEnd) + (DateSerial(year(dteEnd), month(DOB), day(DOB)) > dteEnd)

End Function

Bob

 

[ImLost]

Do you know why it works for the one but not the other?

EDITED - Duh, I feel so stupid. I looked and looked and looked at the codes over and over and still missed the typo. When I changed "28" to "26," I left an 8 in there. Now that it's out, it's working. Gees. Thanks for your help tho.

I plugged this one in to try it out:

DateDiff("yyyy",[DOB],[Forms]![ReportMenu]![Text26])+(DateSerial(Year([Forms]![ReportMenu]![Text26]),Month([dob]),Day([DOB]))>[Forms]![ReportMenu]![Text26])

but a message came up saying it couldn't validate the records and nothing was updated.

 

[raskew]

Would the original function work if I reversed < to >? Do you know why it works for the one but not the other?

No, that's not the solution. The Int(Format(Forms!ReportM enu!Text268,"mmdd")<Format([dob],"mmdd"))
is a Boolean statement that equates to -1 if True, 0 if False.

If what you posted is a direct copy/paste from your query SQL, your problem may be a typo:

For the beginning query, I have:
DateDiff("yyyy",[DOB],Forms!ReportMenu!Text26)+Int(Format(Forms!ReportM enu!Text268,"mmdd")<Format([dob],"mmdd"))

(where text26 contains the begin date)

Also, what about the space in:

Forms!ReportM enu!Text268

Bob

 

[ImLost]

No, that's not the solution. The Int(Format(Forms!ReportM enu!Text268,"mmdd")<Format([dob],"mmdd"))
is a Boolean statement that equates to -1 if True, 0 if False.

If what you posted is a direct copy/paste from your query SQL, you problem may be a typo:



Also, what about the space in:

Forms!ReportM enu!Text268

Bob

Yeah, I figured it out and edited my post just before you replied. It was the typo. I think the space is just the result of copying and pasting to the thread because it's not really there.

Thanks again and please have a laugh at my expense. I appreciate that you guys took the time to try and help me! Although apparently I am beyond help.

 

[Brianwarnock]

Thanks again and please have a laugh at my expense. I appreciate that you guys took the time to try and help me! Although apparently I am beyond help.

I wont be laughing I spotted that when your problem was first posted but thought, "hey its a typo as he posted, it would fail bigtime if it was in his code" so did nothing. I feel a right jerk now, just shows that one shouldn't be afraid to point out the simple things.

Brian