How to sort by Day of Week 3

[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

 

[Blorf]

First, you have to get rid of the Date field in your query.

Replace it with two fields, one for month, the other for day, so you can list any given month:

Month:format ([Rent_date], "YYYY-MM&quot (This will return year and month, so you can sort if you ever need to)

And

Day: weekday([Rent_date])

You can do a sum on the day field to get totals.

CaZ




Ascii dumb question, get a dumb Ansi

 

[SteveR77]

Apply a format to your date field and then group on that field instead.

Format([Mydate],"dddd&quot will give you the day name.

HTH,

Steve

 

[frontside]

How do I format date as weeknr?

I want to format a date field from yyyymmdd to yyyyww

ex 2005-10-31 to 2005-44 (44=weeknr)

Any ideas anyone?

Mikael nilsson
Sweden

 

[RoyVidar]

[tt]format(yourdate, "yyyy-ww")
Year(yourdate) & "-" & datepart("ww", yourdate)[/tt]

-though you might have problems with the "ww" thingie, as some of those constants are localized. Vecka is week in Sweedish, right? Try with "vv" if "ww" doesn't work. Look up the format or datepart function in the help files, in case it gives incorrect weeknumber (dependent on the optional firstweekofyear arguement).

Perhaps

[tt]format(yourdate, "yyyy-ww",,3)[/tt]

Roy-Vidar