How to sort by Day of the Week

[hawkeye71]

Hi Folks:
The table has rent_date column in the form of mm/dd/yyyy. Table date format can not be changed. I need to find the total number of cars rented in a month, grouped by day of the week. I am trying to write a query, so that, the query should not return more than 7 rows - one row for each day of the week.
I am using MS Access 97. I tried to get the day of that date by using a temporary field - rent_dayay([rent_date])
, this worked as far as giving me just the day. However, when I try to use the Group By on this field, it is still grouping by date (1/1/2004,1/2/2004,..) and not by day (Monday,Tuesday,..).
All help/suggestions are welcome.


Thanks,
Indiana

 

[Golom]

Have you tried

   GROUP BY Day([rent_date])

?

 

[dhookom]

Actually Day() will return the Day of the Month. Weekday() will return the day of the week. Other alternatives for the day of the week are
Format([Rent_Date],"ddd&quot
Format([Rent_Date],"dddd&quot

Duane
MS Access MVP
Find out how to get great answers faq219-2884.