Calculations on user form

[M.Eagan]

Hi. I have a question regarding calculations on the data entry form. If you
have a person's date of birth and you want to know what their age is on June
1 2004 (this will be a constant endpoint), and you want this to be
calculated on the user input form and stored in the main data table along
with all the other data from the form, is this possible to do? I.e. Can I
have two fields:
Date of Birth (which I have specified to be the short form of the date, but
I could alter this) and then have another field that ACCESS automatically
fills in with the calculated age on June 1, 2004, once Date of Birth is
entered?
Thanks for any help.

 

[Meagan]

Thanks for this information. Re: Your recommendation- Is it preferable to
save the calculated fields for queries?

First, I will begin this reply with a comment I make often... I do not

recommend storing calculated amounts in data fields. Unless you make sure
that amount is calculated and stored again often, you cannot guarantee its
accuracy.

That having been said, you can use the DateDiff function to calculate

differences in dates. The Microsoft Knowledgebase has a lot of info on
"Calculating and Comparing Time/Date Data". Once you decide the format and
the formula, you should attach its value to the After Update property of the
birthdate field. It may look something like this:

[AgeField]= DateDiff("yyyy",[BirthDate],#6/1/2004#)

The above example will give whole year results, use "d" instead of "yyyy" to get the number of days.

Hope this helps.


----- M.Eagan wrote: -----

Hi. I have a question regarding calculations on the data entry form. If you
have a person's date of birth and you want to know what their age is on June
1 2004 (this will be a constant endpoint), and you want this to be
calculated on the user input form and stored in the main data table along
with all the other data from the form, is this possible to do? I.e. Can I
have two fields:
Date of Birth (which I have specified to be the short form of the date, but
I could alter this) and then have another field that ACCESS automatically
fills in with the calculated age on June 1, 2004, once Date of Birth is
entered?
Thanks for any help.

 

[Douglas J. Steele]

Unfortunately, the code you posted will be wrong for roughly half of the
people: all those who are born after June 1st.

[AgeField]= DateDiff("yyyy",[BirthDate],#6/1/2004#) - _
IIf(Format([BirthDate], "mmdd") > "0601", 1, 0)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

First, I will begin this reply with a comment I make often... I do not

recommend storing calculated amounts in data fields. Unless you make sure
that amount is calculated and stored again often, you cannot guarantee its
accuracy.

That having been said, you can use the DateDiff function to calculate

differences in dates. The Microsoft Knowledgebase has a lot of info on
"Calculating and Comparing Time/Date Data". Once you decide the format and
the formula, you should attach its value to the After Update property of the
birthdate field. It may look something like this:

[AgeField]= DateDiff("yyyy",[BirthDate],#6/1/2004#)

The above example will give whole year results, use "d" instead of "yyyy" to get the number of days.

Hope this helps.


----- M.Eagan wrote: -----

Hi. I have a question regarding calculations on the data entry form. If you
have a person's date of birth and you want to know what their age is on June
1 2004 (this will be a constant endpoint), and you want this to be
calculated on the user input form and stored in the main data table along
with all the other data from the form, is this possible to do? I.e. Can I
have two fields:
Date of Birth (which I have specified to be the short form of the date, but
I could alter this) and then have another field that ACCESS automatically
fills in with the calculated age on June 1, 2004, once Date of Birth is
entered?
Thanks for any help.

 

[Douglas J. Steele]

Yes: in virtually all cases, the calculations should be done in a query, not
stored in the table.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for this information. Re: Your recommendation- Is it preferable to
save the calculated fields for queries?

First, I will begin this reply with a comment I make often... I do not

recommend storing calculated amounts in data fields. Unless you make sure
that amount is calculated and stored again often, you cannot guarantee its
accuracy.

That having been said, you can use the DateDiff function to calculate

differences in dates. The Microsoft Knowledgebase has a lot of info on
"Calculating and Comparing Time/Date Data". Once you decide the format and
the formula, you should attach its value to the After Update property of the
birthdate field. It may look something like this:

[AgeField]= DateDiff("yyyy",[BirthDate],#6/1/2004#)

The above example will give whole year results, use "d" instead of

"yyyy"
to get the number of days.

Hope this helps.


----- M.Eagan wrote: -----

Hi. I have a question regarding calculations on the data entry

form.
If you

have a person's date of birth and you want to know what their age

is
on June

1 2004 (this will be a constant endpoint), and you want this to be
calculated on the user input form and stored in the main data table along
with all the other data from the form, is this possible to do? I.e. Can I
have two fields:
Date of Birth (which I have specified to be the short form of the date, but
I could alter this) and then have another field that ACCESS automatically
fills in with the calculated age on June 1, 2004, once Date of

Birth

is
entered?
Thanks for any help.

 

[Zippy the Pinhead]

Yes: in virtually all cases, the calculations should be done in a query, not
stored in the table.

Maybe I misunderstood the original poster's question in which case I
apologize.

Wasn't s/he wanting to display the calculated value (age as of a
certain date) on a user FORM? In that case, couldn't it be calculated
"on the fly" and displayed on the form in an unbound textbox with the
control source being an expression calculating the desired value?
This would be calculated and displayed when the form is open and the
birthdate entered, and would go away when the form went to a new
record.

This would give the information to the user doing the data entry and
not violate the principle that calculated values shouldn't be stored
in a table.