I would like to calculate an age based on a a particular point in a year. For little league, your age as of April 30th is used to determine how old you are for the entire year. So someone born 3/5/2004 is considered 9, will some born 5/5/2004 is considered 8. I can do this in Excel using =DATEDIF(A1,"5/1/" & YEAR(NOW()),"y"). That works great. My problem is access doesn't recognize DATEDIF. I have a text box called txtDOB which holds the birthday. I am trying to enter into the control source of a text box on a form. any ideas?

I get invalid syntax. also, I need the date to caluclate from April 30th in the year of the birthday. That's the piece I am having trouble. I assume I have to hard code "4/30" and concatenate with the year in the birthday.

< also, I need...>
Lets stick to the original question for now...

So also try:
Datediff("Y", cdate(me.txtDOB), Date())

0

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

The year is caclulating but it is doing it off of calendar year. It doesn't recognize 4/30. Dates will only change from 12/31.... 1/1. when year changes

If this is not what you want, then it would be clearer if you just posted some sample reference dates, then posted the expected "Age".

0

marku24Author Commented:

sorry this is so difficult. Is is basically saying that a year, for age purposes, ends on April 30th and not on December 31st. It is how old you are as of April 30th.

Here is an example using 3/3/2004 as a birthdate:

As of 4/30/2013 this person is 9 because they had their 9th birthday already.

Here is an example using 5/3/2004 as a birthdate:

As of 4/30/2013 this person is 8 because they did not have their 9th birthday yet.

0

marku24Author Commented:

If you put birthdate in cell A1 in Excel, this formula does the calculation correctly.

Ok, so "Close enough" does not work for you?
... ;-)

<sorry this is so difficult. >
No, it is not you, ...it was just not clear to me what you were really after..
But in reading through all your posts, I understand now, ...so it is I who will apologize..
;-)

In any event I am out of time on this one...
I have contacted the "Date" Expert in this zone...
I am sure he will be along shortly...
;-)

First, you should always handle dates as dates, not strings, not numbers, no exceptions.

Then, there is no direct way to calculate this in VBA.
However, you can easily create your own dedicated function for the purpose and use it like this:

=AgeLeague(DateValue([txtDOB))

Public Function AgeLeague(ByVal datDob As Date) As Integer Dim intAge As Integer Dim datLeague As Date datLeague = DateSerial(Year(Date), 4, 30) intAge = Years(datDob, datLeague) AgeLeague = intAgeEnd FunctionPublic Function Years( _ ByVal datDate1 As Date, _ ByVal datDate2 As Date, _ Optional ByVal booLinear As Boolean) _ As Integer' Returns the difference in full years between datDate1 and datDate2.'' Calculates correctly for:' negative differences' leap years' dates of 29. February' date/time values with embedded time values' negative date/time values (prior to 1899-12-29)'' Optionally returns negative counts rounded down to provide a' linear sequence of year counts.' For a given datDate1, if datDate2 is decreased step wise one year from' returning a positive count to returning a negative count, one or two' occurrences of count zero will be returned.' If booLinear is False, the sequence will be:' 3, 2, 1, 0, 0, -1, -2' If booLinear is True, the sequence will be:' 3, 2, 1, 0, -1, -2, -3'' If booLinear is False, reversing datDate1 and datDate2 will return' results of same absolute Value, only the sign will change.' This behaviour mimics that of Fix().' If booLinear is True, reversing datDate1 and datDate2 will return' results where the negative count is offset by -1.' This behaviour mimics that of Int().' DateAdd() is used for check for month end of February as it correctly' returns Feb. 28. when adding a count of years to dates of Feb. 29.' when the resulting year is a common year.'' 2000-11-03. Cactus Data ApS, CPH.' 2000-12-16. Leap year correction modified to be symmetrical.' Calculation of intDaysDiff simplified.' Renamed from YearsDiff() to Years().' 2000-12-18. Introduced cbytMonthDaysMax.' 2007-06-22. Version 2. Complete rewrite.' Check for month end of February performed with DateAdd()' after idea of Markus G. Fischer. Dim intDiff As Integer Dim intSign As Integer Dim intYears As Integer ' Find difference in calendar years. intYears = DateDiff("yyyy", datDate1, datDate2) ' For positive resp. negative intervals, check if the second date ' falls before, on, or after the crossing date for a full 12 months period ' while at the same time correcting for February 29. of leap years. If DateDiff("d", datDate1, datDate2) > 0 Then intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2)) intDiff = Abs(intSign < 0) Else intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1)) If intSign <> 0 Then ' Offset negative count of years to continuous sequence if requested. intDiff = Abs(booLinear) End If intDiff = intDiff - Abs(intSign < 0) End If ' Return count of years as count of full 12 months periods. Years = intYears - intDiffEnd Function

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.