Compute the difference between dates

R

Remi

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.
 
R

Rick Brandt

Remi said:
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.
Click to expand...

Check help file for DateDiff() function.

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

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
 
R

Remi

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.
 
R

Rick Brandt

Remi said:
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.
Click to expand...

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.
 
R

Remi

Worked like a charm. Thanks!

Jeff Boyce said:
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

Remi said:
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.
Click to expand...
Click to expand...
 
D

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!)


Jeff Boyce said:
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

Remi said:
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.
Click to expand...
Click to expand...
 
K

Ken Sheridan

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

Douglas J. Steele said:
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!)


Jeff Boyce said:
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

Remi said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

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!)


Ken Sheridan said:
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

Douglas J. Steele said:
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!)


Jeff Boyce said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Top