Managing Age Junior/Adult

[Nick]

I have a two-part question to handle our club memberships.

(1) When I am entering a new member's details into a
form, I input their Date of Birth. What I need to do is
calculate their age, to determine whether they are adult
or junior members. The value for the age at which a junior
becomes an adult is from >=[tbYear]![Age]. Adult or junior
result needs to be entered into the field called Class on
the same form. If the date is changed the result needs to
be re-calculated in the Class field.
(2) What I also need to do with the details once
entered is to set-up an automated system so that as junior
members becomes of age, which is set, as above, by >=
[tbYear]![Age], their Class field is up-dated to Adult or
up-dates if the Date of Birth or the >=[tbYear]![Age] is
changed manually. This also needs to un-check a checkbox
called Printed in the same table if there is a change in
the Class field.

I have tried using the following but with little success:
This gives me their age - Int(DateDiff("d",[Date of
Birth],Now())/365.25)

=IIf([Age] >=(=DLookUp"Age","tbYear")), "ADULT", "JUNIOR")
but no luck.

This is a big ask but I hope someone can help.

Regards
Nick

 

[Rick B]

First, your age calculation is wrong. There are not 365.25 days in a year.
I wish people would stop using that formula. It is not correct. Use...


DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(
),"mmdd"))


Secondly, what is the age where the membership changes? Why are you reading
it from a table? If it is 16 years old, just use 16 in your calculation.

 

[Nick]

I got the age calculation from a previous post pointing me
to this web site -
http://members.iinet.net.au/~allenbrowne/func-08.html. As
you can probably tell I am a novus with access and only
use the information the I'm shown. Could you please
explain the difference as they both seem to work.

The reason I need to look-up the aged from a table in that
my club has four different organisations that have
different ages for defining their juniors. Over the last 5
years we have had numerous changes and as our community is
extremely transient, if I move on, I would like to set-up
the database to be as user friendly as possible.

Regards
Nick

-----Original Message-----
First, your age calculation is wrong. There are not 365.25 days in a year.
I wish people would stop using that formula. It is not correct. Use...


DateDiff("yyyy",[Birthdate],Date())+(Format ([Birthdate],"mmdd")>Format(Date(
),"mmdd"))


Secondly, what is the age where the membership changes? Why are you reading
it from a table? If it is 16 years old, just use 16 in your calculation.

--
Rick B

I have a two-part question to handle our club memberships.

(1) When I am entering a new member's details into a
form, I input their Date of Birth. What I need to do is
calculate their age, to determine whether they are adult
or junior members. The value for the age at which a junior
becomes an adult is from >=[tbYear]![Age]. Adult or junior
result needs to be entered into the field called Class on
the same form. If the date is changed the result needs to
be re-calculated in the Class field.
(2) What I also need to do with the details once
entered is to set-up an automated system so that as junior
members becomes of age, which is set, as above, by >=
[tbYear]![Age], their Class field is up-dated to Adult or
up-dates if the Date of Birth or the >=[tbYear]![Age] is
changed manually. This also needs to un-check a checkbox
called Printed in the same table if there is a change in
the Class field.

I have tried using the following but with little success:
This gives me their age - Int(DateDiff("d",[Date of
Birth],Now())/365.25)

=IIf([Age] >= (=DLookUp"Age","tbYear")), "ADULT", "JUNIOR")
but no luck.

This is a big ask but I hope someone can help.

Regards
Nick

.