date difference in days between birthdate and now

[jean]

Hi

I have a field formatted dd/mm/yy like 25/11/74 representing 25 of
november 1974

I want to calculate the number of days between that day and today

Exemple if today is the 20/11/10 answer would be 5
if today is the 28/11/10 answer would be -3

thanks for helping

 

[John Spencer]

Take a look at the VBA function DateDiff.

DateDiff("D",[SomeDate],Date())

If you want the difference between just the day and month then you can use the
DateSerial function to transform your date to a date in the current year Or
use the DateAdd function.

DateDiff("D",DateSerial(Year(Date()),Month([SomeDate],Day([SomeDate]),Date())

You could replace the DateSerial expression with the following
DateAdd("YYYY",Year(Date())-Year([SomeDate]),Date())

Those two would behave slightly different with February 29 of any year. Using
DateAdd would return February 28 for non-leap years. DateSerial would return
March 1 for non-leap years.
2008-02-29 would convert to 2010-02-28 with DateAdd
and to 2010-03-01 with DateSerial.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi

I have a field formatted dd/mm/yy like 25/11/74 representing 25 of
november 1974

If this is a Date/Time field, the format is irrelevant - a Date is actually
stored as a number, a count of days (and fractions of a day, times) since
midnight, December 30, 1899. The format merely controls how that number is
displayed.

I want to calculate the number of days between that day and today

Exemple if today is the 20/11/10 answer would be 5
if today is the 28/11/10 answer would be -3

DateDiff("d", [fieldname], Date())

will calculate the integer number of days (that's the "d", you can use other
units, see the VBA help for Datediff) between the arguments.

If your field is a Text field you'll need to convert it to a date/time value
in the calculation.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[a a r o n . k e m p f]

Select EmployeeID, (Date()-BirthDate)/365.25 as age
From Employees

Take a look at the VBA function DateDiff.

    DateDiff("D",[SomeDate],Date())

If you want the difference between just the day and month then you can use the
DateSerial function to transform your date to a date in the current year Or
use the DateAdd function.

DateDiff("D",DateSerial(Year(Date()),Month([SomeDate],Day([SomeDate]),Date())

You could replace the DateSerial expression with the following
DateAdd("YYYY",Year(Date())-Year([SomeDate]),Date())

Those two would behave slightly different with February 29 of any year. Using
DateAdd would return February 28 for non-leap years.  DateSerial would return
  March 1 for non-leap years.
2008-02-29 would convert to 2010-02-28 with DateAdd
and to 2010-03-01 with DateSerial.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have a field formatted dd/mm/yy like 25/11/74 representing 25 of
november 1974

I want to calculate the number of days between that day and today

Exemple if today is the 20/11/10 answer would be 5
if today is the 28/11/10 answer would be -3

thanks for helping