Datediff function

A

Alex Woodhams

Hi there,
I have been wanting to automate an AGE box on my current form, which i have
managed to do, however even though the calculated age appears on the form it
doesnot appear on the corresponding table. What am i doing wrong?
Many thanks
 
D

Douglas J. Steele

You shouldn't be storing the age.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

Simply put your Age calculation as a computed field in a query, and use that
query wherever you would otherwise have used the table.
 
M

missinglinq via AccessMonster.com

Granted, the storage of a calulated field is often something to be
discouraged, but what if the calculated age was to be, say, the age of a
patient when they were *first* seen by a physician, as opposed to their age
when the record is viewed at a later date?
 
D

Douglas J. Steele

I'd prefer storing the date of first visit (and recalculating the age, even
if it's redundant) rather than storing the age at first visit. I believe the
date would be more useful for other purposes.

However, if you did decide to store a date like that, you could set the
field's value in VBA code once you knew the patient's DOB.
 
F

fredg

Granted, the storage of a calulated field is often something to be
discouraged, but what if the calculated age was to be, say, the age of a
patient when they were *first* seen by a physician, as opposed to their age
when the record is viewed at a later date?
Click to expand...

Then one would use a [DateFirstSeen] field in the table instead of
Date() to determine the age when first seen.

Since the OP hasn't said what his formula for determining the actual
Age would be, and a simple DateDiff will not be accurate, here is a
generic expression that will accurately determine current 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:
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)
 
B

Bob Quintal

Granted, the storage of a calulated field is often something to be
discouraged, but what if the calculated age was to be, say, the
age of a patient when they were *first* seen by a physician, as
opposed to their age when the record is viewed at a later date?
Click to expand...
You calculate the age between DateOfBirth and DateOfFirstVisit instead
of between DateOfBirth and Now() /1

/1 Date() would be better, but I like the immediacy of now in hte
expression.
 
Top