date selection update query

[mountainwombat]

I have inherited an access database used to book training rooms. The the booking can be made for any day and the duration of the booking can be up to 13 days.



the booking table has the following fields:

roomno,employeeid,startdate,enddate,numberemployee s,numbernonemployees


The reporting requirement is to have a weekly report showing the bookings for this week, ordered by room. This is simple enough


I use the following to select the records with bookings for the week

[startdate]=Between [reportdate] And [reportdate]+6 or [enddate]=Between [reportdate] And [reportdate]+6

This gives me the records I want for the report, one per each booking. But they also want a check box for each day of the week ticked if the room is booked for that day.

the table has boolean fields for each day:

roomno,employeeid,startdate,enddate,numberemployee s,numbernonemployees,sun, mon, tues, wed, thurs, fri, sat

I want to update the records to set the day field to true for the days of the week for this weeks report.

The problem is that a booking may have a startdate in one week and the enddate in the next, so I need to dynamically update the record to have the booleans ticked only for the days in this reporting week.

the reports are for a single week only, I need to show which rooms are booked for the days of one week.


The previous persons solution was to add a reportdate field to each record, the user had to enter the date of the saturday of that week. if a room was to be booked over a weekend the user had to enter two records, one for each week, and to click the booleans for each day that the room is being used in each record, and they have to reenter all the other details on each record. you can imagine how happy the users are to do two records for a single booking

Any help would be greatly appreciated

[alansidman]

Solution provided here

[mountainwombat]

not meaning to be thick, how do I close the thread as solved?