Access "Week" Query

[neilscaplan]

I need to create a combobox in Access that allows the user to choose the week of the year for a report to print. I want the week number to be the bound column, although it can be hidden. I need something like this:-

Week Number | date for first Day of week "to" date for last day of week

The first day of week and last day of week can be one column, or two...

Does anyone know a) how to do this, or b) if there is another way (e.g. an activeX control)?

[Ecniv]

When does your week start? Monday/Sunday?

the rest is finding which week the user wants per year. Calculation probably.

If the first week of the year starts on wednesday is that actually the first week?

depends how you are picking the week and year as well.
You could work off the users current date to specify the year (not good around the end of the end tho).
You could have weeks 1-52 and a year selection.

if you chose the latter, all you need to do is find that week.
number of days would be (week * 7) + 1 jan year. Then calc the day you are on and when the week began.

Have a go and post up if it doesn't work right. (post your code)

[neilscaplan]

Being an old fashioned sort i'd rather the week started on a Sunday. However, this is so that a person can reconcile what they think they should be paying in rent (a percentage of weekly turnover) and what the landlord thinks. The landlord's week starts on a Tuesday!

I have been playing around with the calendar activeX control, which seems to simplify things, but you can ony highlight a day rather than a week. I know you only need a day to work out the week and year, but it doesn't look very elegant. Is there a way to force the control to highlight a week rather than a day (much like you can in Outlook)?

The problem with the coding ideas is that the rental situation has had issues in the past, and the person I am writing this for may need to delve all the way back to sort this out, so using the system day won't necessarily work...

[Ecniv]

I have not used the calendar activex so I am unsure whether you can highlight more than one day.

Assuming you can, you'd probably still need the dates to highlight from and to.

'---- in VBA - immediates window
?cdate("10 mar 2007")-(weekday(cdate("10 mar 2007"),vbTuesday)-1)

I think that should give you the start of the week.

dteFirstOfYear=cdate("1 jan 2007")+(8-weekday(cdate("1 jan 2007"),vbTuesday ))

To find the first tuesday of the year (and the first date in the year of the first week)

bytWeekNum=((cdate("10 mar 2007")-dteFirstOfYear) \ 7)+1

To give you the week.

Have a play with it and see.



As to the activex, if you get the start and end date (or the start date of the week) you can loop 7 times and highlight all the dates... If you can multi highlight that is.

[neilscaplan]

Thanks for that, I will have a play and let you know.

I suppose if the worst comes to the worst I can always use the activex twice, once for the start date of the period and once for the end date. That may not be very elegant, but it might actually make it more flexible. They won't have to specify a week, they could go for a month for example...mmm... interesting...