DateDif functions (I think)

  • Thread starter Thread starter Sarah McPhillips
  • Start date Start date
S

Sarah McPhillips

Hello,
I'm only new to using Access (as in a few weeks) and I was wondering
if anyone could help me. I want to run a query that will give me the
age of my recorded patients. I have a DOB column and am unsure how to
(1) insert the current date to compare with the DOB of the patients
and; (2)insert the correct information into the function.

I'm predicting that I need the DateDif but I'm not sure how to set it
out correctly in the Query Design. I've previously done this in Excel
with
=(DateDif(E4, TODAY(), "D"))365.25
and wish to achieve the same result.

Can anyone provide an example to assist me or give the command that I
must insert into the design format???
Thanyou for your time and hope to hear some advice!!!
Sarah
 
You can find a function here that will give you a person's age.

http://www.mvps.org/access/datetime/date0001.htm

You could then call this function from your query. To do this, you would create a
calculated field in the query. Where you would normally place the field name, you would
instead place something like:

PatientAge: Age([Table1].[DOB], Date())

When you open the query, the field will have the name PatientAge and will be filled in
with the age of the patients. This is using the 2nd example on the page linked above.
 
I'm predicting that I need the DateDif but I'm not sure how to set it
out correctly in the Query Design. I've previously done this in Excel
with
=(DateDif(E4, TODAY(), "D"))365.25
and wish to achieve the same result.
Click to expand...

Well, Excel is one program, Access is another one.

You do need to (annoyingly) open the VBA editor (by editing a Module
or pressing Ctrl-G) in order to see the online help for DateDiff. If
you want an *accurate* age in years, wherein the age increases by one
at midnight on the person's birthday, use the expression:

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

The IIF clause corrects for the year-off if the birthday has not yet
arrived. If you need years and fractions of a year, use

Age: DateDiff("d", [DOB], Date()) / 365.25

and be aware that leapyears will cause this to be a day out at times
around the person's birthday.
 
Back
Top