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
GROUP BY Records.BADGE;

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?
 
Ad

Advertisements

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
GROUP BY Records.BADGE;

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top