Calculate date

[CoachBarkerOJPW]

RIght now in a form I have this calculation to find the date of birth. I
would like to replace the variable of current season with a selection from a
combo box (cboSeasonID)but can not seem to get it right. Any help would be
apppreciated.

Private Sub txtDateOfBirth_AfterUpdate()
'read in the birth date and the current season
Dim dateOfBirth As Date
Dim currentSeason As Date
' declare the variables
dateOfBirth = txtDateOfBirth.Value
currentSeason = txtCurrentSeason.Value
' calc the age
Dim PlayingAge As Integer
PlayingAge = DateDiff("yyyy", dateOfBirth, DateSerial(Year(Date), 8, 1) -
IIf(Format(dateOfBirth, "mmdd") < "0801", 1, 0)) - 1
' put the age on the form
txtPlayingAge.Value = PlayingAge
End Sub

Thanks
CoachBarkerOJPW

 

[Jeff Boyce]

It isn't clear to me why you are referring to "txtCurrentSeason.Value" if
you want to use a combo box's value ... A more typical naming convention
would have you refer to either cboCurrentSeason (if the 1st column holds the
data) or cboCurrentSeason.Column(n) (if the n-1 column in the combo box
holds the value.

Then once you have the "current season" being read, your code doesn't refer
to it again in your calculation. For you to know "what age as of season X",
you'd need to know what date to use for "season X"...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[CoachBarkerOJPW]

txtCurrentSeason is a serial date that the calculation is made from. The code
in the post is the way it is now, but I need to get rid of the serail date
and use a date selected from the combo box (cboSeasonID). It would read
something like this and the date in the combo box will always be 1 Aug of the
current year;

Private Sub txtDateOfBirth_AfterUpdate()

 

[Jeff Boyce]

So, what happens when you run the code?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[JK]

Coach,

Looks like horses aging (????)

txtDateOfBirth is a *text*, how do you store the date?
You will need to convert the text into date.
Below I assumed that it stored in a way that CDate() will reconize it, if
this is not the case, you will need to convert it by other means.


Private Sub txtDateOfBirth_AfterUpdate()

'read in the birth date and the current season
Dim dateOfBirth As Date
Dim currentSeason As Date

' declare the variables
dateOfBirth = CDate(txtDateOfBirth.Value) '***** changed
currentSeason = txtCurrentSeason.Value '*** does not seem to be used

' calc the age
Dim PlayingAge As Integer
PlayingAge = DateDiff("yyyy", dateOfBirth, Date) _
- IIf(DatePart("y", Date) < DatePart("y", DateSerial(Year(Date), _
8, 1)), 1, 0)

'Removed
'PlayingAge = DateDiff("yyyy", dateOfBirth, DateSerial(Year(Date), 8, 1)
_
'- IIf(Format(dateOfBirth, "mmdd") < "0801", 1, 0)) - 1
' put the age on the form

txtPlayingAge.Value = Trim(Str(PlayingAge))

End Sub

Regards/JK

 

[CoachBarkerOJPW]

Let me ask this question first. When I first did this project a couple of
years ago, I asked if there was a way to have a date that automatically would
set every year to the 1 of August of the current year. I was told to use a
serial date. This is a registration data base I did for a local Pop Warner
Football Association. The playing age is determined by the age of the player
as of July 31 (Current Year). I am no longer involved with this Association
but want to be able to leave them with a usable data base registration
system. Last season the date in the Current Season text box was 1 Aug 2006,
is it safe to assume that the same text box will set to 1Aug 2007 this year.
This is the date used to calculate the playing(this date minus Date of Birth)
Is that what a serial date will do? If so then I do not need to make any
modifications.

Thanks
CoachBarkerOJPW

 

[JK]

Coach,

The DateSerial() function converts year, month as day to a date, you have
used it correctly. To determine the age of a player as of 31 July in the
Current year use:

PlayingAge = Year(Date) - Year(dateOfBirth) - 1 _
+ IIf(Format(dateOfBirth, "mmyy") <= "0731", 1, 0)

'(Note + IIF(Formt(etc. not - IIF( etc)


The DatePart() function will out of wack by one day if the player was born
on 31 July in a leap year *and* the current year is *not* a leap year or
vice versa, therefore I changed back to your original Format() function

You still need to address the fact that txtDateOfBirth is a String that
needs to be converted to a date

Regards/JK

 

[JK]

Oops

PlayingAge = Year(Date) - Year(dateOfBirth) - 1 _
+ IIf(Format(dateOfBirth, "mmdd") <= "0731", 1, 0)

(error in the Foramat() function was "mmyy")