how to group the date as the first day of the month and quarter

[jefferyzhong]

hi, I have a problem need help
I want to group the data by the first day of the month and the first day of the quarter,
grouped by the first day of month, such like: 03/15/05, 03/21/05, will be grouped as 03/01/05
grouped by the first day of quarter, such like: 01/15/05, 03/12/05, 02/19/05, will be grouped as 01/01/05

thanks in advance

 

[PHV]

grouped by the first day of month
GROUP BY DateSerial(Year([Date field]),Month([Date field]),1)
grouped by the first day of quarter
GROUP BY DateSerial(Year([Date field]),1+3*((Month([Date field])-1)\3),1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886