Start report weeks on Fridays, display dates for the week

[Correenie]

Hello all!

I have another challenge.

Our work week runs Friday-Thursday.

I need to create a report called "Tours for January" and instantly have the following things happen:

1) The weeks of the month are separated Friday-Thursday.
2) The date range is displayed for viewers.

The "____" in the example below will be replaced with the field [Tour Date]

Example of what should be shown:

January 2007

1/1/2007 - 1/4/2007 Total number of tours = ____
1/5/2007 - 1/11/2007 Total number of tours = ____
1/12/2007 - 1/18/2007 Total number of tours = ____
1/19/2007 - 1/25/2007 Total number of tours = ____
1/26/2007 - 1/31/2007 Total number of tours = ____

I don't mind if I have to hand-type different dates in the report -- show me one example and I'll happily duplicate it. I tried (and failed) to group it by week where (in the query) the month is >#12/31/2006# and <#2/1/2007# (but the week the report gives me is Sunday-Saturday, and that won't do).

Many thanks for any help!

 

[PeterF]

If I understand your question correctly you want to group on workweeks and count the number of Tours between dates.

The format function allows to start a week on a Friday, to give you a week number you can write a custom function like

Function CustomWeek(DateToWeek As Date) As Integer
    CustomWeek = Format(DateToWeek, "ww", vbFriday, vbUseSystem)
End Function

or use a calculated field with the format function in a query to group your Workweeks.

If you choose to use a custom function like above, You can calculate the number of tours with a function like this:

Function NumberOfTours(Startdate As Date, Enddate As Date) As Integer
    NumberOfTours = CustomWeek(Enddate) - CustomWeek(Startdate) + 1
End Function

 

[Correenie]

Thank you for your help -- unfortunately, after much strife, I figured it out yesterday (this, beyond a count of tours, is the VPG per week for a certain resort). I'm posting it here because I had to splice nearly 50 different websites of sample code to make it work in my report.

=Sum(IIf([Tour Date] Between #1/12/2007# And #1/18/2007# And [Hilton Head]=True,[Purchase Price]))/-Sum(IIf([Tour Date] Between #1/12/2007# And #1/18/2007# And [Hilton Head]=True,[Toured]+[Not Qualified]))