How do I display week numbers when the week runs Saturday to Frida

[milly54]

Hi,
I'm working with a holiday-park database, where the booking week runs from
Saturday to Friday - (and may also have midweek bookings traversing a
weekend).

I'm trying to report their bookings/values by week number, but as the ISO
week is Monday to Sunday, I get incorrect/split reporting.

The week numbering does NOT have to agree with calendar week numbers, just a
numerical value incrementing through the booking year (eg from Sat 5th Jan
2000 to Fri 26th Dec 2008)

I've tried using: DatePart("ww",[Booking_Start],7) but of course this is
only good if the booking starts on a Saturday.

Is there a way to achieve this?

Many thanks

 

[Klatuu]

Well, for starters, Jan 5, 2000 was on a Wednesday. Jan 5, 2008 is a Saturday.
You don't define your rules very well, so an answer is a bit difficult. For
example, assuming you meant Week 1 should start on 1/5/2008, the your
expression should be:
datepart("ww",#1/5/2008#,vbSaturday,vbFirstFullWeek)

That would mean that 1/1 through 1/4 would return 52, being in the previous
year.
Any other option wouls return 2.

I've tried using: DatePart("ww",[Booking_Start],7) but of course this is
only good if the booking starts on a Saturday.

Not really. It depends on how you define which week a booking belongs to.
It is not possible a booking could cross a week boundry? for example, a
booking from

8/8/2008 through 8/14/2008 (using the expression above) would start in week
32 and end in week 33. That is a friday to friday booking. But even if a
booking starts on a saturday, it could still run over a week boundry, so the
issue is to determine the business rule on which week the booking belongs to.


--
Dave Hargis, Microsoft Access MVP

Hi,
I'm working with a holiday-park database, where the booking week runs from
Saturday to Friday - (and may also have midweek bookings traversing a
weekend).

I'm trying to report their bookings/values by week number, but as the ISO
week is Monday to Sunday, I get incorrect/split reporting.

The week numbering does NOT have to agree with calendar week numbers, just a
numerical value incrementing through the booking year (eg from Sat 5th Jan
2000 to Fri 26th Dec 2008)

I've tried using: DatePart("ww",[Booking_Start],7) but of course this is
only good if the booking starts on a Saturday.

Is there a way to achieve this?

Many thanks