Date Calculations

[Guest]

I am a volunteer for a rescue group and in our database, we usually have a
birth date, but would like to have a subsequent field calculate the actual
age in years and months (ie: 2 yrs 5 months) How do I set up a calculation
for this?

 

[Guest]

Hi, Becky.

would like to have a subsequent field calculate the actual
age in years and months (ie: 2 yrs 5 months) How do I set up a calculation
for this?

You could create a new query and paste the following SQL statement into the
SQL View pane:

SELECT PersID, FName, LName, DOB,
(DateDiff("yyyy", DOB, Date()) + Int(Format(Date(), "mmdd") < Format(DOB,
"mmdd"))) AS Age,
(DateDiff("m", DOB, Date()) - (Age * 12) + Int(Format(Date(), "dd") <
Format(DOB, "dd"))) AS Mos,
Age & " years " & Mos & " months" AS YrsAndMos
FROM tblPersons;

Replace PersID with your table's primary key field, FName with the first
name field, LName with the last name field, DOB with the birth date field,
and tblPersons with your table name. Save the new query.

Next, create a new form with this query as the record source, and all of the
fields in this query will be displayed. The "YrsAndMos" text field will
always display the age in years and months, as long as there is a date of
birth in the DOB field. There's no need to save this value in any table,
because it can always be calculated as of today's date, no matter what date
"today" is.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

 

[Guest]

What if I want to enter a formula that references the DOB directly into a new
field on an existing form - is that possible?

 

[Guest]

Hi, Becky.

What if I want to enter a formula that references the DOB directly into a new
field on an existing form - is that possible?

Yes. Add the calculated values to the existing form's Record Source
property (either directly in the query's SQL statement or the Record Source's
SQL statement):

(DateDiff("yyyy", DOB, Date()) + Int(Format(Date(), "mmdd") < Format(DOB,
"mmdd"))) AS Age,
(DateDiff("m", DOB, Date()) - (Age * 12) + Int(Format(Date(), "dd") <
Format(DOB, "dd"))) AS Mos,
Age & " years " & Mos & " months" AS YrsAndMos

.... then add a new control to the form while in Design View by selecting the
YrsAndMos field from the Field List. Alternatively, you could create a new
text box on the form and set the Control Source as YrsAndMos.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.

 

[Guest]

Gunny - Maybe I should have mentioned that I'm a beginner at
Access....Anyway, I copied your formula into my field and changed the
"tblPersons" to my table name, but when I go back to the form view, all I get
in that field is: #Name?
What am I doing wrong? Am I supposed to change the "yyyy" to current year?
Do I still put in the SELECT info at the beginning of the formula?

 

[Guest]

Hi, Becky.

Maybe I should have mentioned that I'm a beginner at
Access....

Then welcome to Access! May I suggest posting future questions in the "New
Users" forum, where answers are more tailored for new users, usually
step-by-step. All of the other Access newsgroups generally supply answers
where the questioner is expected to be more advanced, since the questioner
would otherwise be posting in the "New Users" forum if the questioner was a
beginner. You may find the Microsoft Online Communities portal for new users
on the following Web page:

http://www.microsoft.com/office/com...ft.public.access.gettingstarted&lang=en&cr=US

I copied your formula into my field and changed the
"tblPersons" to my table name, but when I go back
to the form view, all I get in that field is: #Name?

Am I supposed to change the "yyyy" to current year?
Do I still put in the SELECT info at the beginning of the formula?

Since you are a new user, I suspect that you are not using a query as the
Record Source of the bound form, but the table instead. Using a query as the
Record Source provides much flexibility, such as sorting and filtering,
joining with other tables, and the ability to expand as needed.

If your form is not bound to a query, then I'd suggest creating a new query
and using that as the Record Source for the form. Open the new query in SQL
View, then paste the following SQL statement into it:

SELECT *, (DateDiff("yyyy", DOB, Date()) + Int(Format(Date(), "mmdd") <
Format(DOB, "mmdd"))) AS Age,
(DateDiff("m", DOB, Date()) - (Age * 12) + Int(Format(Date(), "dd") <
Format(DOB, "dd"))) AS Mos,
Age & " years " & Mos & " months" AS YrsAndMos
FROM tblPersons;

(Replace the DOB field name with your table's birthdate field name, and
replace the tblPersons table name with your table's name.) Save this new
query.

Open your form in Design View and open the Properties dialog window. Select
the "Data" tab, then select the "Record Source" combo box and scroll down and
select the new query's name.

Select the new text box you created on your form while it's still in Design
View. In the Properties dialog window, select the "Data" tab, then select
the Control Source combo box and scroll down and select YrsAndMos.

Save the form and open it in Form View. The calculated years and months
will be displayed in the text box in your form.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.