Calculating the league age based on an end date

[jdub via AccessMonster.com]

Hey group!!

I need some help. I've been tasked to calculate the age of players from
1/1/2006. I created a field in my registration table called "enddate" and
gave it a default value of "1/1/2006." During registration, the user enters
the Date of Birth and it calculates the players league age. The formula I'm
using is: =Int(DateDiff("m",[DOB],[EndDate])/12).

The problem I'm having is that it works for every player except the ones in
January. What I would like to do is expand this to include the day as well.
Presently, the formula only has "m" for month. Please help. I'm a newbie
and happened to stumble across the above formula online but can't remember
where.

thanks,

Jdub

 

[Douglas J. Steele]

DateDiff("yyyy", [DOB], #1/1/2006#) - IIf(Format([DOB], "mmdd") > "0101", 1,
0)

 

[jdub via AccessMonster.com]

Thanks Douglas,

I'm receiving a Syntax error (comman) in query expression message when I run
the report.

DateDiff("yyyy", [DOB], #1/1/2006#) - IIf(Format([DOB], "mmdd") > "0101", 1,
0)

Hey group!!

[quoted text clipped - 17 lines]

Jdub

 

[Douglas J. Steele]

I can't see how that calculation could be causing a syntax error.

Does the query run on its own, and it's only the report that complains, or
do you get the error running the query as well?

If you get the error running the query, what's the SQL for your query (open
the query in design mode, select SQL View from the View menu, copy the SQl
and paste it here).

If it's strictly the report, do you have calculations as the control source
for any of the text boxes?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas,

I'm receiving a Syntax error (comman) in query expression message when I run
the report.

DateDiff("yyyy", [DOB], #1/1/2006#) - IIf(Format([DOB], "mmdd") > "0101", 1,
0)

Hey group!!

[quoted text clipped - 17 lines]

Jdub