Date help

[GingGangGoo]

I have a yearly event that I keep data for and last year I received help on here in setting up a working db for it. The problem is I need to make some changes that will allow me to use this every year, adding records as needed.

My first challenge is that I set up a query with this field
Date: Switch([Shift] Like "W?","8/1/12",[Shift] Like "T?","8/2/12",[Shift] Like "F?","8/3/12",[Shift] Like "S?","8/4/12") Where the letters, W, T, F and S = the days of the week. I need to be able to change the year without going in and changing this every time.

I thought setting up a junction table connecting YrID table where a list of years lives, to the Shift table where the shifts live, in a table ShiftYear might work as the shifts don't change from year to year, i.e there are always 4 days, 3 shifts and the days are always Wed - Sat. The only thing that changes, obviously are the dates. I did this but I'm not sure what to do next.

The query with this field runs for a subform on my main worker form, allowing me to see and edit each worker's shifts. Obviously, I need to see only the shifts for the current year (once I am able to add them) and to filter out workershift data from 2012.

I hope this makes sense -
TIA

 

[Brianwarnock]

You could try replacing this

/12 with
/[enteryear]

But if it works you will probably be prompted 4 times if so then use a form with a text box formatted for integer and then

/Forms!yourformname!yourtextboxname

Leaving the form open you will not get prompted. You can actually place a command button on the form to run the report or query


Brian