Divide in a weekly format

[REDaughdril]

I have a report, based on a query that I want to divide the categories on a weekly format from an input from the query. When the form starts it would ask for the starting and ending date and then divide the time up in to week one, week two, week three and week four.
Thanks

 

[GumbyD]

Try this little function I created:

Public Function WeekAssign(SDate As Date, EDate As Date, ValueDate As Date) As String

If ValueDate < SDate Or ValueDate > EDate Then
WeekAssign = "Out of Range"
Else
WeekAssign = "Week " & ((ValueDate - SDate) \ 7) + 1
End If

End Function

The expression in your query should look something like the following:

Week: WeekAssign([Enter Start Date],[Enter End Date],IIf(IsNull([YourDateField]),#12/31/9999#,[Your Date Field]))

In the report you should be able to just group on the "Week" field.

Good Luck!

GumbyD