convert date to week, starting at financial year

[OTWarrior via AccessMonster.com]

I have query that counts how many bookings were made on a certain date, but I
need to total it by each week (for example 5 on monday and 2 on wednesday,
week 1 total = 7)

I have worked out that you use: Format([dteDate],"ww") for the week, but I
need to set the earliest date selected as week one, as this would be the
start of the financial year (earliest date is April first)

how would I set this up? would I need a table with all the dates in with the
week number next to it?

 

[Guest]

My recommendation would be to use the DateDiff( ) function in combination
with the DateSerial function; something like the following. Getting the
right year depends on how you number your Fiscal Years, the following example
starts FY08 on 1 April 07

(DateDiff("d", DateSerial(Year(MyDate) + (Month(MyDate)<4), 4, 1), MyDate)
\7) + 1

Replace "MyDate" with the name of your DateField.

HTH
Dale

 

[Jamie Collins]

I have query that counts how many bookings were made on a certaindate, but I
need to total it by each week

would I need a table with all the dates in with the
week number next to it?

That's how I'd do it. See:

Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519


Jamie.

--