Group and Sum Friday-Sunday Orders But Not Other Days

M

mike

Hi there. I have a table that lists all types of orders of
products that are shipped out. The query is qryShipped and
the important fields are InvoiceDate and DollarsShipped.
I'm trying to group all the shipments by date and sum the
dollars shipped on each date. The tricky part is that all
shipments for Friday-Sunday are considered as one day. So,
I would have Mon, Tues, Wed, Thurs, Fri-Sun. I can't seem
to figure out how to make an exception that groups Fri-
Sun. Any suggestions would be great. Thanks!
 
A

Allen Browne

Type this expression into the Field row of your query:
WhatDay: IIf(WeekDay([InvoiceDate],2) >= 5, 5, Weekday([InvoiceDate],2))

Group on that expression.
 
M

mike

Allen, Thanks!! Looks like this works great. It's a big
help!
-----Original Message-----
Type this expression into the Field row of your query:
WhatDay: IIf(WeekDay([InvoiceDate],2) >= 5, 5, Weekday ([InvoiceDate],2))

Group on that expression.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Hi there. I have a table that lists all types of orders of
products that are shipped out. The query is qryShipped and
the important fields are InvoiceDate and DollarsShipped.
I'm trying to group all the shipments by date and sum the
dollars shipped on each date. The tricky part is that all
shipments for Friday-Sunday are considered as one day. So,
I would have Mon, Tues, Wed, Thurs, Fri-Sun. I can't seem
to figure out how to make an exception that groups Fri-
Sun. Any suggestions would be great. Thanks!
Click to expand...


.
Click to expand...
 
Top