Hi all,
I am trying to create a form in a spreadsheet style that displays room bookings over a period of time.
Now I have read countless topics on this forum and have managed to create the form (based on a query of the room table & bookings) as a continuous form so that each room is displayed in the left column.
I then created a range of unbound fields across the top of the form to contain the date and some navigation buttons to enable the user to scroll through the dates.
I have another raft of unbound fields next to each room on the continuous form that compare the room and the date field and then display whether or not it is booked.
The control source of each of the unbound fields is :
=IIf([Text4]=[fldBookIn],"IN",IIf([Text4] Between [fldBookIn] And ([fldBookOut]-1),"Booked",""))
The next unbound field would be :
=IIf([Text5]=[fldBookIn],"IN",IIf([Text5] Between [fldBookIn] And ([fldBookOut]-1),"Booked",""))
and so on
So what I have now looks like this :
......... Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8
Room1 ...IN ..booked
Room1 .............................IN ..booked
Room1 .............................................IN ....booked booked
Room2 ...IN booked
Room2 ......................................IN ...booked booked
what I would like is the form to look like this:
...........Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8
Room1 ....IN booked ............IN ...booked IN ..booked booked
Room2 ....IN booked ......................IN ..booked
Can any assist with the code or design on how to get the room bookings to all appear on only one line. I suspect I need to re-adjust the control source of the unbound fields, but am struggling to know where to start
Basically i am looking for pointers on how to group all the results in the query to the room name and show all the booking information on one line
I hope I have explained this well enough, if not, please let me know
Thanks in advance for your help and advice
I am trying to create a form in a spreadsheet style that displays room bookings over a period of time.
Now I have read countless topics on this forum and have managed to create the form (based on a query of the room table & bookings) as a continuous form so that each room is displayed in the left column.
I then created a range of unbound fields across the top of the form to contain the date and some navigation buttons to enable the user to scroll through the dates.
I have another raft of unbound fields next to each room on the continuous form that compare the room and the date field and then display whether or not it is booked.
The control source of each of the unbound fields is :
=IIf([Text4]=[fldBookIn],"IN",IIf([Text4] Between [fldBookIn] And ([fldBookOut]-1),"Booked",""))
The next unbound field would be :
=IIf([Text5]=[fldBookIn],"IN",IIf([Text5] Between [fldBookIn] And ([fldBookOut]-1),"Booked",""))
and so on
So what I have now looks like this :
......... Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8
Room1 ...IN ..booked
Room1 .............................IN ..booked
Room1 .............................................IN ....booked booked
Room2 ...IN booked
Room2 ......................................IN ...booked booked
what I would like is the form to look like this:
...........Date1 Date2 Date3 Date4 Date5 Date6 Date7 Date8
Room1 ....IN booked ............IN ...booked IN ..booked booked
Room2 ....IN booked ......................IN ..booked
Can any assist with the code or design on how to get the room bookings to all appear on only one line. I suspect I need to re-adjust the control source of the unbound fields, but am struggling to know where to start
Basically i am looking for pointers on how to group all the results in the query to the room name and show all the booking information on one line
I hope I have explained this well enough, if not, please let me know
Thanks in advance for your help and advice
