Calculating ages in query

  • Thread starter Thread starter Chris H
  • Start date Start date
C

Chris H

Can anybody tell me how to calculate an age from a date of
birth. I am in the UK so DOB is in dd/mmm/yyyy format.
Also in the query I have 2 dates. How can I get the query
to tell me the time between these 2 dates in days.

IE Date 1 01-jan-2003
Date 2 30-jan-2003

Need the time in days between 1 and 2

Also I need these answers to be in a separate box, ie the
date of birth firld, and next to it, the age. The same
with the days query


Many thanks

Chris H
 
Can anybody tell me how to calculate an age from a date of
birth. I am in the UK so DOB is in dd/mmm/yyyy format.
Click to expand...

The Format is irrelevant in this case. No matter how a date/time is
formatted, and no matter what the computer's regional settings, a
Date/Time value is stored as a Double Float number. The format is just
for display.
Also in the query I have 2 dates. How can I get the query
to tell me the time between these 2 dates in days.

IE Date 1 01-jan-2003
Date 2 30-jan-2003

Need the time in days between 1 and 2

Also I need these answers to be in a separate box, ie the
date of birth firld, and next to it, the age. The same
with the days query
Click to expand...

Set the Control Source of the [Age] textbox to

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

DateDiff will calculate the difference in whole years (e.g. someone
born anytime in 1953 will have 50 as the result); the IIF expression
subtracts one year if the person's birthday has not yet arrived.

In the Control Source of the Days textbox use

=DateDiff("d", [Date 1], [Date 2])

Open the VBA editor by typing ctrl-G (this links you to the needed
Help file) and search the online help for DateDiff for more
information about this useful function (and its kin, DateAdd and
others).
 
Back
Top