subtracting 2 dates to get the difference

  • Thread starter Jennifer Connors
  • Start date
J

Jennifer Connors

I have a report that has 2 colums with dates in them. I
need to subtract the 2 dates and get the difference
between them and put that new number in another column.
Is this to be done in the report or does it need to be
done in the query?

Thanks
 
R

Roger Carlson

It can be done either place. I tend to put it in the query, but I don't
think there is an advantage either way.
 
J

Jim/Chris

In a new column in query
result:[date1]-[date2]
The advantage of doing it in a Query is you can do
additional calculations in the report(like average)

Jim
 
R

Roger Carlson

Sure. The DateDiff function is the best way. With it, you can find the
difference of two date in years, month, days, quarters, etc.

I assume you want days, and so in a column of your Query put something like
this:
DifferenceDate: DateDiff("d",[StartDate],[EndDate])

On the report (if you don't want to do it in the query) you would put
something like this in the ControlSource of a textbox:
=DateDiff("d",[StartDate],[EndDate])

NOTE: if you get a negative value from the above, reverse the order of the
fields. I can never remember which one has to be the larger! <grin>
 
J

Jennifer Connors

And where do I enter this information? In the design
view? And in which field?
-----Original Message-----
In a new column in query
result:[date1]-[date2]
The advantage of doing it in a Query is you can do
additional calculations in the report(like average)

Jim
-----Original Message-----
Do you know how to do it?
Click to expand...
wrote in message
.
Click to expand...
.
Click to expand...
 
J

Jennifer

It worked, but now I am getting negative numbers. The
negative numbers are from the dates that are in the same
year. I have some dates that are from last year to this
year and they are the ones that are negative. Can I do
this equation with the months?
-----Original Message-----
Sure. The DateDiff function is the best way. With it, you can find the
difference of two date in years, month, days, quarters, etc.

I assume you want days, and so in a column of your Query put something like
this:
DifferenceDate: DateDiff("d",[StartDate],[EndDate])

On the report (if you don't want to do it in the query) you would put
something like this in the ControlSource of a textbox:
=DateDiff("d",[StartDate],[EndDate])

NOTE: if you get a negative value from the above, reverse the order of the
fields. I can never remember which one has to be the
Click to expand...
 
N

Niklas Östergren

Jennifer said:
It worked, but now I am getting negative numbers. The
negative numbers are from the dates that are in the same
year. I have some dates that are from last year to this
year and they are the ones that are negative. Can I do
this equation with the months?
-----Original Message-----
Sure. The DateDiff function is the best way. With it, you can find the
difference of two date in years, month, days, quarters, etc.

I assume you want days, and so in a column of your Query put something like
this:
DifferenceDate: DateDiff("d",[StartDate],[EndDate])

On the report (if you don't want to do it in the query) you would put
something like this in the ControlSource of a textbox:
=DateDiff("d",[StartDate],[EndDate])

NOTE: if you get a negative value from the above, reverse the order of the
fields. I can never remember which one has to be the
Click to expand...
larger! said:
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org





.
Click to expand...
Click to expand...
 
N

Niklas Östergren

Hi Jennifer!

Yes you can!
=DateDiff("m",[StartDate],[EndDate])
Click to expand...

To read about function DateDiff open up code window <View / Code>

Type (anyware) "DateDiff" without "". Set cursor anyware in the text
(DateDiff) and press F1. This will open up helpfile for this function. There
you can read all about what you can do with this function!

// Niklas



Jennifer said:
It worked, but now I am getting negative numbers. The
negative numbers are from the dates that are in the same
year. I have some dates that are from last year to this
year and they are the ones that are negative. Can I do
this equation with the months?
-----Original Message-----
Sure. The DateDiff function is the best way. With it, you can find the
difference of two date in years, month, days, quarters, etc.

I assume you want days, and so in a column of your Query put something like
this:
DifferenceDate: DateDiff("d",[StartDate],[EndDate])

On the report (if you don't want to do it in the query) you would put
something like this in the ControlSource of a textbox:
=DateDiff("d",[StartDate],[EndDate])

NOTE: if you get a negative value from the above, reverse the order of the
fields. I can never remember which one has to be the
Click to expand...
larger! said:
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org





.
Click to expand...
Click to expand...
 
Top