# Min/Max Dates only looking at month?

A

#### Allie

I have a list of employee absences and I'd like to find the number of days
between the earliest and latest dates. It almost works....
I have a min/max set up as follows:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records

If a particular employee has absences for all one year, the query returns
the results perfectly fine. But in the example below, where the earliest and
latest dates span over 2 years - the days are wrong. Ex:

2/5/08
10/9/08
1/3/09
It should find the days between 2/5/08 and 1/3/09.
Instead, it picks 1/3/09 as mindate and 10/9/08 as maxdate. I believe
because of the MONTHS.

How can I get it consider the year first? Or is there a better way to do this?

A

#### Allie

Everytime I pressed submit it said ERROR, NOT POSTED
But, thanks for the concern

tkelley via AccessMonster.com said:
4 different posts?
I have a list of employee absences and I'd like to find the number of days
between the earliest and latest dates. It almost works....
I have a min/max set up as follows:

SELECT Records.BADGE, Sum(Records.DAYS) AS SumOfDAYS, Min(Records.DATE) AS
MinOfDATE, Max(Records.DATE) AS MaxOfDATE,
Abs(DateDiff('d',[MINOFDATE],[MAXOFDATE])) AS DIFFERENCE
FROM Records

If a particular employee has absences for all one year, the query returns
the results perfectly fine. But in the example below, where the earliest and
latest dates span over 2 years - the days are wrong. Ex:

2/5/08
10/9/08
1/3/09
It should find the days between 2/5/08 and 1/3/09.
Instead, it picks 1/3/09 as mindate and 10/9/08 as maxdate. I believe
because of the MONTHS.

How can I get it consider the year first? Or is there a better way to do this?