A date function in Access using VB

[Guest]

I need to create a function that will calculate an age from a form. The
function needs to subtract the dateOfBirth from the currentSeason. Is there a
way to set the currentSeason to change every year, example this year would be
8/01/2006 and next year would automatically be 8/01/2007. I have a field on
the form for the season (that is a list of years), a field for date of birth,
and a field for age.

 

[Douglas J. Steele]

If it's always going to be August 1st of the current year, use
DateSerial(Year(Date()), 8, 1) to determine currentSeason.

To figure the age, use:

DateDiff("yyyy", DOB, DateSerial(Year(Date()), 8, 1) - IIf(Format(DOB,
"mmdd") < "0801", 1, 0)

What this does is determine the difference in years between the two dates.
However, the DateDiff function is a little too literal: to it, there's 1
year between 31 Dec, 2006 and 1 Jan, 2007. That means you want to subtract 1
from the difference in years that you just calculated, which is what the IIf
statement determines.

 

[Guest]

It has been soem time but I just got back into this. Does it matter it the
DOB and the playingAge are bound to a database. Right now it has all been set
up and I am working on an existing project. What we want is to not have to
compute the playingAge by hand. In your expression DOB, should this be
formatted as DOB as it is in the database or should it be txtDOB as it is on
the form. Also does it belong in the control source for the text box as an
expression starting with = .
Thanks

 

[Douglas J. Steele]

You should be able to refer to either the field in the form's recordsource
(which, if I'm reading correctly, is DOB), or to the text box that's bound
to that field. In either case, use Me. in front (Me.DOB or Me.txtDOB)

Yes, if you're going to use that formula as the control source for an
unbound text box, you'd put an = sign in front of it.

You do realize, I hope, that you shouldn't be storing the age in the table.

 

[Guest]

Maybe a foolish question why not store the age in the table? The from is
based on a table for players and one of the fields is the players age.

 

[Douglas J. Steele]

Because calculated values should never be stored.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

 

[Guest]

Thanks, took me awhile to get back to you, it looks like this and it works,
still have a few bugs to work out with an If statement but will continue to
work on it.

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))

' put the age on the form

txtPlayingAge.Value = PlayingAge

End Sub

 

[Douglas J Steele]

Your parentheses are incorrect. It should be:

PlayingAge = DateDiff("yyyy", dateOfBirth, DateSerial(Year(Date), 8, 1)) -
IIf(Format(dateOfBirth, "mmdd") < "0801", 1, 0)

The first part

DateDiff("yyyy", dateOfBirth, DateSerial(Year(Date), 8, 1))

tells you how many "year changes" there have been. The second part

IIf(Format(dateOfBirth, "mmdd") < "0801", 1, 0)

reduces that calculated "year changes" by one if the birthday hasn't
happened yet.

Looking back through the thread, it would look as though I made the typo in
the first place. Sorry about that!

 

[Guest]

Thanks, but shouldn't the comparison operator be > becasue the birthdate of
that year hasn't happened yet. In other words if the birthday is after Aug 1
of the current year, then the age would be what the age is as of Aug 1. Or do
I have it backwards.

 

[Douglas J. Steele]

Sorry, you're right. That's not my normal birthday calculation: I must have
been having a bad day when I first responded to you!

Here's the function I typically use:

Function Age(DOB As Date, Optional WhatDate) As Long
If IsMissing(WhatDate) Then WhatDate = Date
Age = DateDiff("yyyy", DOB, WhatDate) - IIf(Format$(WhatDate, "mmdd") <
Format$(DOB, "mmdd"), 1, 0)
End Function

My apologies.

 

[Guest]

No problem you set me in the right direction and I was able to work it from
the there. How about this I need to assign a value to a text box based on the
persons age and weight. I know it is not quite right because it does not do
anyhting to the txtOlderLighter text box,but then it doesn't throw any
errors.

Private Sub txtWeight_AfterUpdate()

' read in the age and the weight
Dim age As Integer
Dim weight As Integer
Dim olderLighter As Integer

' assign the age and the weight a value
age = txtPlayingAge.Value
weight = txtWeight.Value


If age = 11 And weight <= 80 Then

olderLighter = 1

ElseIf age = 12 And weight <= 95 Then

olderLighter = 1

ElseIf age = 13 And weight <= 110 Then

olderLighter = 1

Else

olderLighter = 2

End If

' Assign a value using the single-line form of syntax.
If olderLighter = 1 Then Me.txtOlderLighter.Value = "Yes" Else
Me.txtOlderLighter.Value = "No"

End Sub

 

[Douglas J. Steele]

The If-Then-Else structure at the end should be on mutliple lines:

If olderLighter = 1 Then
Me.txtOlderLighter.Value = "Yes"
Else
Me.txtOlderLighter.Value = "No"
End If

although you can simply that to:

Me.txtOlderLighter = IIf(olderLighter=1, "Yes", "No")

Are you sure the code's actually getting called? Try putting a breakpoint in
it, and trace through how it's executing.

 

[Guest]

I see what you mean, changed it and now it is working 100 percent. I
appreciate all your help, haven't worked a lot in VB in access but learned
VB.net last semester in school and learned JAVA in Eclipse this semester.
Probably would have had to do all the calculations this year for registration
with out your help. In case I never mentioned it this has been used for the
last 4 years by our local Pop Warner Football Association for our
registration in place of the old pen and paper system. Once again thanks.