Grouping cost by periods

[Rich]

Add the following and order by it
Year([qryMnthly].[Date By Month])*12+DatePart("m",[qryMnthly].[Date By Month])-1
change the field names to suit

 

[Fornatian]

[b]Public Function FitsDatePeriod(d As Variant) As Variant[/b]
Dim LastDay As Variant

LastDay = EndOfMonth(d)
Do While WeekDay(LastDay) <> vbSaturday
LastDay = LastDay - 1
Loop

If Day(d) <= Day(LastDay) Then
        
        'If it is less than the Last Saturday in the month then
        'it will be included in the current months period
        FitsDatePeriod = StartOfMonth(d)

Else
        'If a date is equal to greater than the last Saturday in the month
        'then it will be forced forward to be included in the next month's period
        FitsDatePeriod = EndOfMonth(d) + 1

End If

End Function

The function worked for me and returned the day of the first day of the month to which the record date fits.

These are the supporting functions.

[b]Function EndOfMonth(d As Variant) As Variant[/b]
'
' Returns the date representing the last day of the current month.
'
' Arguments:
' D            = Date
  
  EndOfMonth = DateSerial(Year(d), Month(d) + 1, 0)
  
End Function

[b]Function StartOfMonth(d As Variant) As Variant[/b]
'
' Returns the date representing the first day of the current month.
'
' Arguments:
' D            = Date
  
  StartOfMonth = DateSerial(Year(d), Month(d), 1)
  
End Function