Calculating "baseball age" in Access

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?
marku24Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
In Access, Datediff("Y", cdate(me.txtDOB), Now())
0
marku24Author Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
< also, I need...>
Lets stick to the original question for now...

So also try:
 Datediff("Y", cdate(me.txtDOB), Date())
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

marku24Author Commented:
it is in the original question.
0
jerryb30Commented:
Datediff("Y", cdate(me.txtDOB), cdate(year(now() & "-5-1"))
0
Jeffrey CoachmanMIS LiasonCommented:
I know, but lets fix the syntax error first in jerryb30's post, ...then move on to the correct formula...
;-)

because if the syntax for the current date is wrong, then any formula based on that wrong syntax will also produce the same error.

;-)

Jeff
0
marku24Author Commented:
I keep getting #name.  I am trying to break this down into its most basic components.  I even get #name on this:  DateDiff("y",#03/03/2004#,#09/09/2013#)
0
Jeffrey CoachmanMIS LiasonCommented:
in the contolsource you should start it with an "=" sign...


=Datediff("Y", cdate(me.txtDOB), Now())
=Datediff("Y", cdate(me.txtDOB), Date())
=Datediff("Y", cdate(me.txtDOB), cdate(year(now() & "-5-1"))
=DateDiff("y",#03/03/2004#,#09/09/2013#)
0
marku24Author Commented:
sorry, got this to work...  used "yyyy"

=DateDiff("yyyy",[txtDOB],#9/9/2010#)

now it would be getting it to recognize 4/30... thanks for all the help.
0
Jeffrey CoachmanMIS LiasonCommented:
=DateDiff("yyyy",[txtDOB],CDate("4/30/" & Year(Date())))
0
marku24Author Commented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, DateDiff just looks at the calendar Year...

Then what you seem to need is to get the diff in 'months' then round to the nearest year
Something like this:

=Round((DateDiff("m",[txtDOB],CDate("5/1/" & Year(Date()))))/12,0)
0
Jeffrey CoachmanMIS LiasonCommented:
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.

=DATEDIF(A1,"5/1/" & YEAR(NOW()),"y")
0
Jeffrey CoachmanMIS LiasonCommented:
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...
;-)

JeffCoachman
0
jerryb30Commented:
Int(DateDiff("m",[txtDOB],CDate(Year(Now()) & "-4-30"))/12)
0
Gustav BrockCIOCommented:
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 = intAge
    
End Function


Public 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 - intDiff
  
End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marku24Author Commented:
Thank you very much experts.  Everyone on this board has been excellent and helpful.  Both of the solutions proposed worked perfectly.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.