Sort members by age on particular date

[Guest]

I am learning as I go and have created a membership database for a
surflifesaving club. I have used the date of birth field on the form to fill
the age field using DateDiff("yyyy",[Date Of
Birth],#01/01/2006#)+(Format([Date Of
Birth],"mmdd")>Format(#01/01/2006#,"mmdd")), this gives the age on the 1st of
January, required to work out which age group members are in, at the moment I
manually select the age group from a drop down list. I would like the
category to autofill using I think an if function from the result from the
above formula. Using under 13 as nippers, 13-15 as juniors, 16-18 as
intermediates, 19 to 29 as seniors and 30 plus as masters. At present when
accessing the age function I use a query as the result is not contained in
the table. I assume I would need to make a new query for this calculation?
Thanks
Garod

 

[Guest]

Public Function SetAge()

If IsNull(Me.DOB) Then
Me.Age.Caption = ""
Else
Me.Age.Caption = Str$(Int((Date - Me.DOB) / 365.25))
End If

End Function

 

[Guest]

...To continue from my other post, the Age field on the form should be placed
as a label. When it is referred to as a module you refer to it as a caption.
Then it shoudl be able to calculate it automatically.

Hope it helps.

 

[John Vinson]

I am learning as I go and have created a membership database for a
surflifesaving club. I have used the date of birth field on the form to fill
the age field using DateDiff("yyyy",[Date Of
Birth],#01/01/2006#)+(Format([Date Of
Birth],"mmdd")>Format(#01/01/2006#,"mmdd")),

Since it's as of the first of January, it's much simpler than that:

Age: Year(Date()) - Year([Date Of Birth])

this gives the age on the 1st of
January, required to work out which age group members are in, at the moment I
manually select the age group from a drop down list. I would like the
category to autofill using I think an if function from the result from the
above formula. Using under 13 as nippers, 13-15 as juniors, 16-18 as
intermediates, 19 to 29 as seniors and 30 plus as masters. At present when
accessing the age function I use a query as the result is not contained in
the table. I assume I would need to make a new query for this calculation?
Thanks
Garod

You could use an AgeGroups table, or use the Switch function. Put the
above Age calculation in a calculated field in the Query, and on the
form or report use

=Switch([Age] < 13, "Nippers", [Age] <= 15, "Juniors", [Age] <= 18,
"Intermediates", [Age] <= 29, "Seniors", True, "Masters")

Switch() takes arguments in pairs, left to right, and returns the
second member of the first pair for which the first member is True.

John W. Vinson[MVP]

 

[John Spencer]

John,
I had the same thought until I looked at some dates.


DOB: 12/31/2005 Year = 2005 Current Year 2006 Age: 1

Subtracting 1 from the calculation would work except for those born on Jan 1
who would be shortchanged by 1 year

To the original poster.
If if were me, I would use either an IIF or switch statement or better yet a
table with the relevant values

Switch(AgeCalculation<13,"Nippers",AgeCalculation<=15,"Juniors",
AgeCalculation<=18,"Intermediates",...)

IF you make a table you would need two columns, one for age and one for
Title. The simplest method would be to have a table with 100 or so records
with the numbers 0 to 99 and the corresponding titles for each. Then you
would just need to lookup the title based on the age.

I am learning as I go and have created a membership database for a
surflifesaving club. I have used the date of birth field on the form to
fill
the age field using DateDiff("yyyy",[Date Of
Birth],#01/01/2006#)+(Format([Date Of
Birth],"mmdd")>Format(#01/01/2006#,"mmdd")),

Since it's as of the first of January, it's much simpler than that:

Age: Year(Date()) - Year([Date Of Birth])

this gives the age on the 1st of
January, required to work out which age group members are in, at the
moment I
manually select the age group from a drop down list. I would like the
category to autofill using I think an if function from the result from the
above formula. Using under 13 as nippers, 13-15 as juniors, 16-18 as
intermediates, 19 to 29 as seniors and 30 plus as masters. At present
when
accessing the age function I use a query as the result is not contained in
the table. I assume I would need to make a new query for this
calculation?
Thanks
Garod

You could use an AgeGroups table, or use the Switch function. Put the
above Age calculation in a calculated field in the Query, and on the
form or report use

=Switch([Age] < 13, "Nippers", [Age] <= 15, "Juniors", [Age] <= 18,
"Intermediates", [Age] <= 29, "Seniors", True, "Masters")

Switch() takes arguments in pairs, left to right, and returns the
second member of the first pair for which the first member is True.

John W. Vinson[MVP]