Fitler subform by date range?

[freddy k]

Hello,

I am a complete Access novice but have been asked to create a database for work.

I work for a kind of employment agency which sends support staff to various sites throughout the city.

So far I have 3 tables:

Worker
Site
Shift requests

and have linked workerID and siteID into the shift requests table, so that we log which worker covers a shift and which site the shift is at.

I have also created a form and subform. The form shows the worker whilst the subform shows all shifts that the worker is allocated to.

What I want to do and what I am struggling with is this:

I want to create a combo box on the form which filters the subform so that only the shifts within a date range are visible. Basically, we need to be able to see at a glance what shifts a worker has picked up, week by week. I had a play with access 2010 and there seems to be a way of filtering built into the subform. Unfortunately we're running access 97 here and that doesn't seem to be an option.

So, is there a way to have 52 options in a combo box called '2nd - 8th May', '9th - 15th May' etc... which when selected would filter the results in the subform?

In addition, is there a way to then show on the form the total hours that a worker has been allocated in a week, which would change as different weeks were selected? I have a field in the shifts table for the shift hours.

If anyone could help with this it would be greatly appreciated. There seems to be a lot about filtering subforms on the internet but I've not found anything specifically about date ranges and being a novice I lack to knowledge to tweak things to my needs.

Many thanks,

Fred.

 

[ted.martin]

Seeing no one has replied yet, let me give you a few thoughts. Firstly, using Access97 to do what you are asking is going to be hard work as Access 2007/2010 has a rather natty little text box calendar which is ideal for using to filter between a date range. Making a combo box with 52 date ranges is far too much like hard work and anyway, it will be out of date next year.

The problem with forum questions like yours is that what seems a simple question frequently requires a complex answer and given your stated novicicity (is that a word? anyway ...) a response would be very difficult.

Suffice it to say that what you want to do is certainly possible but without a little more knowledge of the basics of access design, then , I do not think you are going to get very far.

Sorry about this opinion but we all had to start somewhere and maybe what you are asking is a little too broad-ranging at present. Just get the key tables working properly, then zip the file and post it here so someone can then look at how best to filter it.

 

[freddy k]

Hi,

Thanks for your reply.

I appreciate what I am trying to do is probably beyond the realms of a novice.

I've tried to attach a zip of the database but it's too big.

The basic table layout is

tblWorker:
WorkerID
First Name
Last name

tblSite:
SiteID
Site name

tblShift:
ShiftID
Date
Shift time
SiteID
Hours
WorkerID

The form then shows worker name in the main form, and the subform shows all shifts they are allocated.

If anyone has any alternative suggestions as to how to filter the form I'd really appreciate it.

Thanks,

Fred.