How to display 1 month's data per week in a report

[neelsfer]

I have data that i have to display in a report on a monthly basis but split up in weeks.
If a month has 28 days then the data will be split up day1 to day7 as week 1; day 8 to day14 as week 2; day15 to day21 as week 3 and day22 to day28 as week 5 etc.
If you have ie 30 days then a 5th week comes into play.
I will use a filter with a start and end date to get ie January 1 to January 31 data, to display.
My main fields in the query are

Month
Rxdate
Facility
PatientType (ie Diabetic or HIV)
Quantity

What i have done so far:
I have tried to calculate the week number and then sort it ascending. At the same time i use Totals and count to add up the Quantity field.

Is there not perhaps a way to sort data based on weeks dynamically and call it perhaps week 1; week 2 etc in that month
In my query i use this to get the week number based on the Rx date

WeekNum: DatePart("ww",[RxDate])

"Totals" are then used to add up the quantities for that specific week number.

The "weeknumber" is not what i would like to display in my report however.
Please assist

[nico5038]

First you need to decide which week the year is starting with.
Sometimes Access will return for 1-1-yyyy a weeknumber 53 (of the previous year), depending on the regional settings.

Personally I would create (e.g. from code) a steering tabel holding all dates with the all "DayDate", the weeknumber and the Month.

Now I would JOIN the RxDate with this table and a group by after filtering the Month will do the math for you, Including "split weeks" between months.

Getting the idea ?

Nic;o)

[Rabbit]

Take the day, integer divide by 7, add 1. That will give you the week number starting with the the first day of the month.

[neelsfer]

thx guys but i am struggling along and want to make it uncomplicated but i am a bit thick i presume.
Is there not a way to design a query similar to this code on a form, that i can then filter and use in the report?

Dim t_Day As Double
t_Day = Day(rxDate)
Select Case t_Day
Case 1, 2, 3, 4, 5, 6, 7, 8
Me.txtboxweeknumber = 1
Case 9, 10, 11, 12, 13, 14, 15, 16
Me.txtboxweeknumber = 2
Case 17, 18, 19, 20, 21, 22, 23, 24
Me.txtboxweeknumber = 3
Case 25, 26, 27, 28, 29
Me.txtboxweeknumber = 4
Case 30, 31
Me.txtboxweeknumber = 5
End Select

It would also help if i could get the "date period" for a week number. (ie 01 June to 07 June for week 26 perhaps)

[Rabbit]

Did you try my suggestion?

Take the day, integer divide by 7, add 1.

Day(dateValue) \ 7 + 1

As far as your date period goes, that's a different question so it should be in a new thread because we only allow one question per thread. As a note though, there is no guarantee that the 26th week of a year starts with June 1st. The first week of a the month of June will always start with June 1st. But the 26th week of a year does not always start with June 1st.

[Rabbit]

Sorry, forgot you need to subtract one from the day first.

(Day(dateValue) - 1) \ 7 + 1

[neelsfer]

thx rabbit it seems to do the trick