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!

 

[Crowley16]

probably the quickest way is to have a list of week dates, or in your case, fridays and thursdays, and then use that table to link to.

you can probably make up a function to work out and display your week dates but it'll be slow compaired to having it in a table.

--------------------
Procrastinate Now!

 

[SgtJarrow]

correenie,

I would suggest taking a look at the datepart function. DatePart has two required and two optional parameters.

The first required is the datepart you want....in your case ww means week number. The second required parameter is the date in question.

The first optional parameter is the day of the week to consider as day one and the other optional parameter is the day of the year to consider the first day of the year.

Read the help file for DatePart.

I don't know if it is specifically what you want but it should be close enough to get you near the answer you seek.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[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]))

This, unfortunately, had to be repeated for every week of the year. But it works.

 

[SgtJarrow]

Glad you got it working....but just to burst your bubble, you will have to update all those again near the end of the year....or add to it. Seems like since you have a solution working, let that slide, but I would take any chance I had to go back and try to find a dynamic solution. I have been in a similar boat...What will happen is near the end of the yar the app will stop working and you will end up spending days trying to figure out why because by then you will not remember the bandaid. Just my two cents...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer