Compute the difference between dates

[Guest]

Does anyone have any tips or ideas on how I can compare two different date
fields? So that if one date is May 1, 2007 and another date is May 18, 2007
I would like to have a field compute the difference of '17' days to another
field.

 

[Rick Brandt]

Does anyone have any tips or ideas on how I can compare two different
date fields? So that if one date is May 1, 2007 and another date is
May 18, 2007 I would like to have a field compute the difference of
'17' days to another field.

Check help file for DateDiff() function.

?DateDiff("d", #May 1, 2007#, #May 18, 2007#)
17

 

[Guest]

Checkout datediff() in the help. that is exactly what is does!

 

[Guest]

By the way I am using Access 2003. Thanks!

 

[Jeff Boyce]

Remi

In a query, use the DateDiff() function (see Access HELP for exact syntax).

Even easier, just use [FirstDate] - [SecondDate] ... if both fields are
date/time datatypes.

By the way, don't bother storing the result. In fact, it is rarely a good
idea to store a calculated value, since you would then need to come up with
a way to ensure that all the components and the result stay synchronized, in
case one of them is changed (e.g., correcting a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I am using the date diff function. I have date one and date two fields but
when I run a report it asks me for the date rather than computing it
automatically for each set of dates.

 

[Rick Brandt]

I am using the date diff function. I have date one and date two
fields but when I run a report it asks me for the date rather than
computing it automatically for each set of dates.

Sounds like you spelled the field name wrong. If the names contain illegal
characters (like spaces) then you have to put square brackets around them.

 

[Guest]

Worked like a charm. Thanks!

Remi

In a query, use the DateDiff() function (see Access HELP for exact syntax).

Even easier, just use [FirstDate] - [SecondDate] ... if both fields are
date/time datatypes.

By the way, don't bother storing the result. In fact, it is rarely a good
idea to store a calculated value, since you would then need to come up with
a way to ensure that all the components and the result stay synchronized, in
case one of them is changed (e.g., correcting a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Does anyone have any tips or ideas on how I can compare two different date
fields? So that if one date is May 1, 2007 and another date is May 18,
2007
I would like to have a field compute the difference of '17' days to
another
field.

 

[Douglas J. Steele]

You may want to use

Int([FirstDate] - [SecondDate])

just in case they contain times as well as dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remi

In a query, use the DateDiff() function (see Access HELP for exact
syntax).

Even easier, just use [FirstDate] - [SecondDate] ... if both fields are
date/time datatypes.

By the way, don't bother storing the result. In fact, it is rarely a good
idea to store a calculated value, since you would then need to come up
with a way to ensure that all the components and the result stay
synchronized, in case one of them is changed (e.g., correcting a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Does anyone have any tips or ideas on how I can compare two different
date
fields? So that if one date is May 1, 2007 and another date is May 18,
2007
I would like to have a field compute the difference of '17' days to
another
field.

 

[Guest]

Doug:

Probably better to use:

DateValue([FirstDate]) – DateValue([SecondDate])

as the time of day on the first date might be later than that on the second.
Assuming that the difference in days is what's required rather than the time
difference rounded down to whole days.

Ken Sheridan
Stafford, England

You may want to use

Int([FirstDate] - [SecondDate])

just in case they contain times as well as dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remi

In a query, use the DateDiff() function (see Access HELP for exact
syntax).

Even easier, just use [FirstDate] - [SecondDate] ... if both fields are
date/time datatypes.

By the way, don't bother storing the result. In fact, it is rarely a good
idea to store a calculated value, since you would then need to come up
with a way to ensure that all the components and the result stay
synchronized, in case one of them is changed (e.g., correcting a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Does anyone have any tips or ideas on how I can compare two different
date
fields? So that if one date is May 1, 2007 and another date is May 18,
2007
I would like to have a field compute the difference of '17' days to
another
field.

 

[Douglas J. Steele]

Valid point, Ken. However, if you're going to use 2 function calls, then you
might as well just use DateDiff!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug:

Probably better to use:

DateValue([FirstDate]) - DateValue([SecondDate])

as the time of day on the first date might be later than that on the
second.
Assuming that the difference in days is what's required rather than the
time
difference rounded down to whole days.

Ken Sheridan
Stafford, England

You may want to use

Int([FirstDate] - [SecondDate])

just in case they contain times as well as dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remi

In a query, use the DateDiff() function (see Access HELP for exact
syntax).

Even easier, just use [FirstDate] - [SecondDate] ... if both fields are
date/time datatypes.

By the way, don't bother storing the result. In fact, it is rarely a
good
idea to store a calculated value, since you would then need to come up
with a way to ensure that all the components and the result stay
synchronized, in case one of them is changed (e.g., correcting a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Does anyone have any tips or ideas on how I can compare two different
date
fields? So that if one date is May 1, 2007 and another date is May
18,
2007
I would like to have a field compute the difference of '17' days to
another
field.