calculate age for each record individually

[cjlatta]

We are using the '=DateDiff' formula from Access and it is working fine
(Acces 2003; XP Service Pack 2). However, it now populates ALL the fields
with the same birthdate and age. We need it to be diferent for each record.
What do we need to modify/change/adjust? Would it be in the design view
and/or properties for that cell?
any help would be appreciated.

 

[BruceM]

Your problem description is short on details such as how you are using
DateDiff, but it sounds as if you are using it as the Control Source of an
unbound text box in a continuous form. In that case, the result is to be
expected.

One way to get the result for each record is to base the form on a query,
and use the DateDiff expression at the top of an empty column in query
design view:
AgeCalc: DateDiff("yyyy",[DateField],Date())

This will give an incorrect age for somebody whose birthday has not yet
occurred this year. To fix it you could do:
AgeCalc: DateDiff("yyyy",[DateField],Date()) + _
(Date < DateSerial(Year(Date()), _
Month([DateField]), Day([DateField]))

Note that the underscores are line continuation markings for clarity here
only. Do not use them in a query.

The idea (which I borrowed from a thread posted yesterday) is that this
expression:
(Date < DateSerial(Year(Date()), Month([DateField]), Day([DateField]))
is true if the birthday has not yet occurred. Access uses the value of -1
for True and 0 for False, so you either subtract one or 0 from the DateDiff
result.

It is similar in concept to some of the things here:
http://www.mvps.org/access/datetime/date0001.htm

 

[Jeff Boyce]

We aren't there. We can't see what you're working on.

Is this happening in a form? In a query? In a report? In code?

If you have a form bound to a table (or query), but this particular control
is unbound, then you haven't saved the value. On the other hand, if you
have DateOfBirth, you don't need to (and probably should NOT) store the
calculated "Age". After all, tomorrow, some of those could be out of date!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cjlatta]

Thanks to those who have replied. I wasn't sure that the question posted
properly, as I recieved an error message while entering the post. I will
pass the replies along to our user and see if either reply fixes the issue.
We'll get back with you shortly.