Min/Max Dates

  • Thread starter Thread starter Allie
  • Start date Start date
A

Allie

Hi Experts,
I have a databse of employee absences. I am trying to query (per badge
number/employee) the sum of days absent, the earliest & latest dates, and
then return the number of days between the two.

This is what I have:
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 the MaxofDATE and MinofDATE are in the same year, everything works
perfectly. But in this example:

Absences
2/5/08
10/5/08
1/5/09

The MaxofDATE should be 1/5/09
The MinodDATE shouldbe 2/5/08

The query however is returning 1/5/09 and min and 10/5/08 as max
Seems like it is looking at the MONTH and not including the year. Any ideas
why?
 
Allie,

1. First point. "Date" is a reserved word in Access and should not be used
as a field name. If you insist on using it, you should always wrap it in
brackets [Date] to ensure that Access/Jet properly interprets it as a field,
not the function.

2. Second, if the query is returning 10/5/08 as the Max date, it is highly
likely that your [Date] field is a text field, and not a date data type.
Check you data structure and if this is the case, you should consider
changing the data type to Date/Time.
BUT ONLY AFTER BACKING UP YOUR DATA.

If your [Date] field is in fact a date/time data type, then post back and
I'll help you modify the query.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top