Day of Week

[Neil]

In a query I would like to be able to show a date as Monday, Tuesday,
Wednesday etc. Now I know I can use the long date, but this does not allow
me to query a large date range and to group by day of week and sum the data
so that I can see:

date range 01/01/07 to 12/31/07 as:

DAY HOURS
Monday 2000
Tuesday 1100
Wednesday 1500
etc.

Thanks for any suggestions. I am not a programmer so VBA code is possible
only if all I have to do is cut and paste it in.

 

[Allen Browne]

If the field is named Date1, type this into the Field row in query design:
Weekday([Date1])

You can then group by this field.

This will generate fields name 1, 2, 3, etc. which is preferable for sorting
than the day names - particularly if you want a crosstab view.

 

[Neil]

Eureka! Not sure why my initial searches of "Day of Week" wern't returning
anything.

 

[Neil]

Oh that is true, thanks for adding that!

Just in case anyone is looking for the function I used for day of week it is:

Format([Date1],"dddd")

If the field is named Date1, type this into the Field row in query design:
Weekday([Date1])

You can then group by this field.

This will generate fields name 1, 2, 3, etc. which is preferable for sorting
than the day names - particularly if you want a crosstab view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In a query I would like to be able to show a date as Monday, Tuesday,
Wednesday etc. Now I know I can use the long date, but this does not
allow
me to query a large date range and to group by day of week and sum the
data
so that I can see:

date range 01/01/07 to 12/31/07 as:

DAY HOURS
Monday 2000
Tuesday 1100
Wednesday 1500
etc.

Thanks for any suggestions. I am not a programmer so VBA code is possible
only if all I have to do is cut and paste it in.