date diff query

  • Thread starter Thread starter Kay Davies
  • Start date Start date
K

Kay Davies

Hello

I am trying to calculate the age of a person based upon 2
fields and am thinking that I should use the datediff
function.

the age needs to be calculated by getting the difference
between the field "SampleReceived" and the
field "DateOfBirth" Each field is in a seperate table but
the tables are linked.

Currently I have set up a select query with all the
appropriate fields including "SampleReceived"
and "DateOfBirth". I have then added a new field which
will be the calculated field, and typed in the following:

Age:DateDiff("General Number",[SampleReceived],
[DateOfBirth])

However when I run this the output in the field is #error

Can anyone suggest where I am going wrong.

Many thanks
Kay
 
Kay,

This should do the trick:

Age: Abs(DateDiff("yyyy", [SampleReceived], [DateOfBirth]) -
IIf(Format([SampleReceived], "mmdd") <= Format([DateOfBirth], "mmdd"), 0,
1))

If you still have problems, you might have to apend the table names:

Age: Abs(DateDiff("yyyy", [Table1].[SampleReceived],
[Table2].[DateOfBirth]) - IIf(Format([Table1].[SampleReceived], "mmdd") <=
Format([Table2].[DateOfBirth], "mmdd"), 0, 1))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Age:DateDiff("General Number",[SampleReceived],
[DateOfBirth])

However when I run this the output in the field is #error

Can anyone suggest where I am going wrong.
Click to expand...

The first argument to DateDiff is a text string identifying the time
unit you want for the difference: the text string "s" means you want
the age in seconds, "m" means months, "yyyy" means years. There is no
time unit "General Number"! You'll also get a negative number if you
fix this; the second argument is the starting date, the third the
ending date of the interval.

You want "yyyy" - but there's a catch. If you have a DateOfBirth of
#12/31/2003# and a SampleRecieved of #1/1/2004# the DateDiff
expression will report that the person is a year old, since the year
has changed between the two time points. To get age as of previous
birthday use

Age: DateDiff("yyyy", [DateOfBirth], [SampleReceived]) -
IIF(Format([SampleReceived], "mmdd") > Format([DateOfBirth]), "mmdd",
1, 0)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top