how to update calculated date in form into table

P

pierce56

I have created database. One of the fields in the form computes age of
client based on dob and date of referral. It computes this information in
form but I also want this information to be recorded on an associated table.
I am not sure how this is done. Any advice welcom.

Thanks

Pierce
 
B

Barry Gilbert

The function for calculating elapsed date/time values is DateAdd. Use it like
this:

DateAdd("yyyy", myDobVariable, Date())

Is this what you are looking for or are you more interested in how to store
a calculated value in a table? In general, I should note, it's not advisable
to store caluclated values if you can help it. It's better to do the
calculation when you present the data in a form or report.

Barry
 
B

Barry Gilbert

Oops! I made a mistake in the previous post. Use DateDiff instead:
DateDiff("yyyy", myDobVariable, Date())

Barry
 
K

Klatuu

As Barry pointed out, storing calculated values is never a good idea. It
wastes time, space, and can easily end up being wrong.

As to calculating a person's age, the DateDiff will not do it for you. An
actual age can be off based the month and day difference between the birth
date and the calculation date. Here is a function that correctly calculates
a person's age.

I will not claim authorship of this because it violates a lot my my personal
coding rules, but it does work.

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = year(DateToday) - year(Bdate) - 1
Else
Age = year(DateToday) - year(Bdate)
End If
End Function
 
F

fredg

I have created database. One of the fields in the form computes age of
client based on dob and date of referral. It computes this information in
form but I also want this information to be recorded on an associated table.
I am not sure how this is done. Any advice welcom.

Thanks

Pierce
Click to expand...

There is no need to store the clients age based upon the date of
referral in your table.
As long as you have the clients DOB and the DateOfReferral stored, any
time you need the clients age, calculate it.

You haven't posted the calculation you are using to accurately compute
the age. I would suggest, if you are not already using it, that you
use:

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

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

In any event, do NOT store this computation.
 
P

pierce56

Hi Barry

This is my first attempt at a data base. I wanted to store the output of
the age calculation because when going into the query and looking to
calculate, say average age for a sub group of clients it only shows tables
and their variables. I have not been able to figure out reports yet. So if
I wanted to calculate average age of clients by yearly qauarters, I have a
stored code for each quarter I can not crosstab this with age as can not find
it. Query does not list forms? Does this make any sense. Just out of
interest why is it not advisable to store computed data into tables. Many
thanks for guidance so far.

Regards

Pierce
 
J

John Vinson

Just out of
interest why is it not advisable to store computed data into tables.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]
 
Top