Age, number of years and months from date of birth/menbership star

[NitaMax]

I am creating a membership database from scratch for the Community Centre
where I work and I want to be able to do two things:

1) have the age of our members worked out automatically from the date of
birth. I have had a look at the entries on the subject but I am a bit
confused about two things (at least):

a- If I am not to have an 'Age' column when creating my table, how I
am going to get the 'Age' field when creating the form and various reports?

b- When and where do I enter the formula that will enable me to have
the members' age worked out?

2) have the number of years and months of membership worked out
automatically from the date our members joined.

So far, I have mearly been a user. Can someone please help me with this?

Thank you

 

[fredg]

I am creating a membership database from scratch for the Community Centre
where I work and I want to be able to do two things:

1) have the age of our members worked out automatically from the date of
birth. I have had a look at the entries on the subject but I am a bit
confused about two things (at least):

a- If I am not to have an 'Age' column when creating my table, how I
am going to get the 'Age' field when creating the form and various reports?

b- When and where do I enter the formula that will enable me to have
the members' age worked out?

2) have the number of years and months of membership worked out
automatically from the date our members joined.

So far, I have mearly been a user. Can someone please help me with this?

Thank you

You would calculate the age in a query, form or report.
To calculate a persons age:

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control on a form or
report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

Just compute it and display it on a form or report, as needed.

To compute the number of years and months of membership, see
Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Duane Hookom]

I'm not sure how you "have the age of our memebers worked out
automatically". However, if you want to display the number of days between
[DateJoined] and today, you can add a text box to a form or report with the
expression:
=DateDiff("d",[Datejoined],Date())
You could also place this in a query as a calculated column:
MemberDays: DateDiff("d",[Datejoined],Date())

As you see, you don't generally need to store the result since you can
always calculate it based on the current date. Doug Steele has a nice
function that returns the number of years, months, days,... at
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Rick B]

You would include your formula in a new column in your query. You would
then use that query as the record source for your forms and reports. The
new column name wil be a field that you can pull to your forms and reports.
For example...

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

You would then have a new field "AGE" that you can use in your reports and
forms.



Here is how to get membership time in months and years...

MembershipDuration: IIf([JoinDate],DateDiff("m",[JoinDate],Date())\12 & "
yrs. " & (DateDiff("m",[JoinDate],Date()) Mod 12 & " mts."),"")

 

[Rick B]

You say you, "have the age of our members worked out automatically from the
date of birth."

You are not storing this in your table are you? You shouldn't. It would
only be accurate when first entered. You should only store the birthdate
and then calculate the age as demonstrated in my other post (and those from
a couple of others).

 

[NitaMax]

Thank you very much for this.

I deleted the age column when I saw the other answers before.

I am sure I will have more questions later!


NitaMax

 

[NitaMax]

Many thanks for taking the time to reply. I shall get on with this now.

NitaMax

I'm not sure how you "have the age of our memebers worked out
automatically". However, if you want to display the number of days between
[DateJoined] and today, you can add a text box to a form or report with the
expression:
=DateDiff("d",[Datejoined],Date())
You could also place this in a query as a calculated column:
MemberDays: DateDiff("d",[Datejoined],Date())

As you see, you don't generally need to store the result since you can
always calculate it based on the current date. Doug Steele has a nice
function that returns the number of years, months, days,... at
http://www.accessmvp.com/djsteele/Diff2Dates.html
--
Duane Hookom
MS Access MVP

I am creating a membership database from scratch for the Community Centre
where I work and I want to be able to do two things:

1) have the age of our members worked out automatically from the date of
birth. I have had a look at the entries on the subject but I am a bit
confused about two things (at least):

a- If I am not to have an 'Age' column when creating my table, how
I
am going to get the 'Age' field when creating the form and various
reports?

b- When and where do I enter the formula that will enable me to have
the members' age worked out?

2) have the number of years and months of membership worked out
automatically from the date our members joined.

So far, I have mearly been a user. Can someone please help me with this?

Thank you

 

[NitaMax]

Thank you very much.

Time to apply this.

NitaMax

I am creating a membership database from scratch for the Community Centre
where I work and I want to be able to do two things:

1) have the age of our members worked out automatically from the date of
birth. I have had a look at the entries on the subject but I am a bit
confused about two things (at least):

a- If I am not to have an 'Age' column when creating my table, how I
am going to get the 'Age' field when creating the form and various reports?

b- When and where do I enter the formula that will enable me to have
the members' age worked out?

2) have the number of years and months of membership worked out
automatically from the date our members joined.

So far, I have mearly been a user. Can someone please help me with this?

Thank you

You would calculate the age in a query, form or report.
To calculate a persons age:

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control on a form or
report:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

Just compute it and display it on a form or report, as needed.

To compute the number of years and months of membership, see
Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[NitaMax]

I was wondering what steps I need to take to have the age appear on the
forms. I would like myself and the other users of this database to enter the
date of birth and be able to have the age appear in the next field without
having to run a query.

I hope I am making sense.

Thanks in advance for your help.

NitaMax

 

[NitaMax]

Actually, you can ignore my last question. I have just done it and it works.

I am sure I will have a whole lot of other questions.

Thank you

NitaMax