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
"milly54" wrote:
> 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
>
>
|
Similar Threads
