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

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

jean wrote:
> 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

On Mon, 25 Oct 2010 03:55:59 -0700 (PDT), jean <(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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



On Oct 25, 6:20*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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
>
> jean wrote:
> > 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