How best to calculate league age?

[TS in FL]

I am building a DB for a softball league. In the fall season, the league age
is calculated from Sept. 1 of the current year, and in the spring season, the
league age is calculated from Sept. 1 of the previous year. I would like
both the DOB field and calculated league age to be stored in a table that
contains registration details for that particular season. What is the best
way for me to handle this?

 

[Graham Mandeno]

Hi TS

Don't store the age, as this can easily be calculated from the DOB. Storing
data that can be calculated from other data is redundant and introduces the
possibility for the two values to get out of step.

The following function will calculate the age at a given date:

Public Function AgeInYears(dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
Else
AgeInYears = Null
End If
End Function

By default it calculates the age as at the current date, but you can specify
any date as the second argument to the function.

For your purpose, you could calculate the season age date as follows:

Public Function SeasonAgeDate( yr as Integer ) as Date
SeasonAgeDate = DateSerial( yr-1, 9, 1 )
End Function

This returns the date of 1 September the previous year, so
SeasonAgeDate( 2009 ) gives 1-Sep-2008, etc.

You could then modify the AgeInYears function to be more specific to your
purpose:

Public Function SeasonAge(BirthDate As Variant, _
SeasonYear As Integer) As Variant
Dim dtAgeAt as Date
If IsDate(BirthDate) Then
dtAgeAt = DateSerial( SeasonYear - 1, 9, 1 )
SeasonAge = DateDiff("yyyy", BirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(BirthDate), Day(BirthDate)))
Else
SeasonAge = Null
End If
End Function

 

[Dale Fye]

Graham,

I don't think the OP wants to use Yr-1, I think he wants an automatic way to
identify the appropriate September, so for the default date, he should use
something like:

DateSerial(Year(Date()) + (Month(Date()) < 6), 9, 1)

this would give 1 Sept of this year, for the months of June-Dec, but would
return 1 Sept of the previous year for the months of Jan - May.

He could then call the AgeInYears function and pass this as the second
argument.

----
Dale

Hi TS

Don't store the age, as this can easily be calculated from the DOB. Storing
data that can be calculated from other data is redundant and introduces the
possibility for the two values to get out of step.

The following function will calculate the age at a given date:

Public Function AgeInYears(dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
Else
AgeInYears = Null
End If
End Function

By default it calculates the age as at the current date, but you can specify
any date as the second argument to the function.

For your purpose, you could calculate the season age date as follows:

Public Function SeasonAgeDate( yr as Integer ) as Date
SeasonAgeDate = DateSerial( yr-1, 9, 1 )
End Function

This returns the date of 1 September the previous year, so
SeasonAgeDate( 2009 ) gives 1-Sep-2008, etc.

You could then modify the AgeInYears function to be more specific to your
purpose:

Public Function SeasonAge(BirthDate As Variant, _
SeasonYear As Integer) As Variant
Dim dtAgeAt as Date
If IsDate(BirthDate) Then
dtAgeAt = DateSerial( SeasonYear - 1, 9, 1 )
SeasonAge = DateDiff("yyyy", BirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(BirthDate), Day(BirthDate)))
Else
SeasonAge = Null
End If
End Function

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am building a DB for a softball league. In the fall season, the league
age
is calculated from Sept. 1 of the current year, and in the spring season,
the
league age is calculated from Sept. 1 of the previous year. I would like
both the DOB field and calculated league age to be stored in a table that
contains registration details for that particular season. What is the
best
way for me to handle this?

 

[Graham Mandeno]

Hi Dale

You're right - I missed the bit about distinguishing between the two
seasons.

But presumably the OP wants to store data from multiple years and seasons,
so the calculation should be done for the year/season in question, not from
the current date.

This would just involve adding another argument to the function so as to
allow for appropriate calculation of dtAgeAt.

This may all be academic anyway, because the OP seems to have lost his/her
way back here <g>
--
Cheers!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I don't think the OP wants to use Yr-1, I think he wants an automatic way
to
identify the appropriate September, so for the default date, he should use
something like:

DateSerial(Year(Date()) + (Month(Date()) < 6), 9, 1)

this would give 1 Sept of this year, for the months of June-Dec, but would
return 1 Sept of the previous year for the months of Jan - May.

He could then call the AgeInYears function and pass this as the second
argument.

----
Dale

Hi TS

Don't store the age, as this can easily be calculated from the DOB.
Storing
data that can be calculated from other data is redundant and introduces
the
possibility for the two values to get out of step.

The following function will calculate the age at a given date:

Public Function AgeInYears(dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
Else
AgeInYears = Null
End If
End Function

By default it calculates the age as at the current date, but you can
specify
any date as the second argument to the function.

For your purpose, you could calculate the season age date as follows:

Public Function SeasonAgeDate( yr as Integer ) as Date
SeasonAgeDate = DateSerial( yr-1, 9, 1 )
End Function

This returns the date of 1 September the previous year, so
SeasonAgeDate( 2009 ) gives 1-Sep-2008, etc.

You could then modify the AgeInYears function to be more specific to your
purpose:

Public Function SeasonAge(BirthDate As Variant, _
SeasonYear As Integer) As Variant
Dim dtAgeAt as Date
If IsDate(BirthDate) Then
dtAgeAt = DateSerial( SeasonYear - 1, 9, 1 )
SeasonAge = DateDiff("yyyy", BirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(BirthDate), Day(BirthDate)))
Else
SeasonAge = Null
End If
End Function

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am building a DB for a softball league. In the fall season, the
league
age
is calculated from Sept. 1 of the current year, and in the spring
season,
the
league age is calculated from Sept. 1 of the previous year. I would
like
both the DOB field and calculated league age to be stored in a table
that
contains registration details for that particular season. What is the
best
way for me to handle this?

 

[TS in FL]

The OP is back! My apologies for the delay. In my defense, I have four kids.

Anyway, yes, the idea is to store details related to each season (either
Spring or Fall), automatically calculating the player's "league age" as of
September 1 of the applicable year. I will give your code a try and hope not
to hurt myself. Thank you both for your time.

Hi Dale

You're right - I missed the bit about distinguishing between the two
seasons.

But presumably the OP wants to store data from multiple years and seasons,
so the calculation should be done for the year/season in question, not from
the current date.

This would just involve adding another argument to the function so as to
allow for appropriate calculation of dtAgeAt.

This may all be academic anyway, because the OP seems to have lost his/her
way back here <g>
--
Cheers!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I don't think the OP wants to use Yr-1, I think he wants an automatic way
to
identify the appropriate September, so for the default date, he should use
something like:

DateSerial(Year(Date()) + (Month(Date()) < 6), 9, 1)

this would give 1 Sept of this year, for the months of June-Dec, but would
return 1 Sept of the previous year for the months of Jan - May.

He could then call the AgeInYears function and pass this as the second
argument.

----
Dale

Hi TS

Don't store the age, as this can easily be calculated from the DOB.
Storing
data that can be calculated from other data is redundant and introduces
the
possibility for the two values to get out of step.

The following function will calculate the age at a given date:

Public Function AgeInYears(dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
Else
AgeInYears = Null
End If
End Function

By default it calculates the age as at the current date, but you can
specify
any date as the second argument to the function.

For your purpose, you could calculate the season age date as follows:

Public Function SeasonAgeDate( yr as Integer ) as Date
SeasonAgeDate = DateSerial( yr-1, 9, 1 )
End Function

This returns the date of 1 September the previous year, so
SeasonAgeDate( 2009 ) gives 1-Sep-2008, etc.

You could then modify the AgeInYears function to be more specific to your
purpose:

Public Function SeasonAge(BirthDate As Variant, _
SeasonYear As Integer) As Variant
Dim dtAgeAt as Date
If IsDate(BirthDate) Then
dtAgeAt = DateSerial( SeasonYear - 1, 9, 1 )
SeasonAge = DateDiff("yyyy", BirthDate, dtAgeAt) + _
(dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(BirthDate), Day(BirthDate)))
Else
SeasonAge = Null
End If
End Function

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


I am building a DB for a softball league. In the fall season, the
league
age
is calculated from Sept. 1 of the current year, and in the spring
season,
the
league age is calculated from Sept. 1 of the previous year. I would
like
both the DOB field and calculated league age to be stored in a table
that
contains registration details for that particular season. What is the
best
way for me to handle this?