James D.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can any one tell me why when I use the datediff function and specify years,
It gives me a date "3/4/1899" nstead of a period of time? Is it my date
format?

The two dates are as follows:
daDob 08/04/1946
daPatientAge 9/21/2005

The code is as follows:
daPatientAge = DateDiff("yyyy", daSpecDate, daDOB)

Any suggestions would be great.

Thx.
 
DateDiff doesn't return a date: it returns a number indicating the number of
years (in this case) between the two dates.

?DateDiff("yyyy", #8/4/1946#, #7/6/2006#)
60

The reason you're get 3/4/1899 is because internally, Access stores dates as
8 byte floating point numbers, where the integer portion represents the date
as the number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of day. I have no idea what value you
passed for daSpecDate, but it must have resulted in DateDiff returning -301.

BTW, see http://www.mvps.org/access/datetime/date0001.htm at "The Access
Web" for how to calculate age. DateDiff is a little too literal: it sees the
difference between 12/31/2005 and 1/1/2006 as 1 year.
 
daPatientAge should be a Long data type, not a date. DateDiff returns the
number of intervals specified between the two dates. Since daPatientAge is a
date type, it will turn the value returned by the DateDiff function into a
date. That is not what you want.
 
Can any one tell me why when I use the datediff function and specify years,
It gives me a date "3/4/1899" nstead of a period of time? Is it my date
format?

The two dates are as follows:
daDob 08/04/1946
daPatientAge 9/21/2005

The code is as follows:
daPatientAge = DateDiff("yyyy", daSpecDate, daDOB)

Any suggestions would be great.

Thx.
Click to expand...

daParentAge will NOT be a date/time value - it will be an integer (and
will not give the correct age in years either, see below).

A Date/Time value is stored as a Double Float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899.
Dates before then are stored as negative numbers.

DateDiff returns an INTEGER - not a date; you don't say what
daSpecDate is (today's date??) but your expression will return an
integer number of years by which daDOB is AFTER daSpecDate.

A correct expression for the age as of the previous birthday is

lngPatientAge = DateDiff("yyyy", daDOB, Date()) - IIF(Format(daDOB,
"mmdd") > Format(Date(), "mmdd"), 1, 0)

This will calculate the years and subtract one if the person's
birthday has not yet occurred.

I am not at all sure what you mean by saying that the patient's age
(daPatientAge??) is 9/21/2005.


John W. Vinson[MVP]
 
Many Thanks, as you wrote it is the datediff function was returning into a
date data type hence the error.

Thanks again.
 
Back
Top