Hi,
I am trying to write a query that does not display a record according to criteria
The database is a room and equipment booking database the form displays the room details and the equipment that is booked to that room.
This is displayed in a continuous form using text boxes that represent an hours time slot so if the equipment is booked form 09:00 - 12:00 the three text boxes representing the time 09:00 - 12:00 are set to change colour.
This is done using conditional formating so the text boxes need to be bound to a table because it is on a continuous form.
In the form header the room booking details are displayed so for example
Room 1
15/3/2010 09:00 - 12:00 (this is the room booking date and the duration)
So for example the records look something like this
Equip1 09:00 10:00 11:00 00:00 00:00 00:00 00:00 00:00 etc
Equip2 00:00 00:00 00:00 00:00 00:00 14:00 15:00 16:00 etc
So Equip1 is booked for one three hourly slot 09:00 - 12:00 which is within the room booking duration
and Equip2 is booked from 14:00 - 17:00 which is not booked within the room booking duration
So if the field has a value the conditional formating displays that field in red if the field is set to 00:00 then the field is displayed in green meaning the equipment is available.
So what I am trying to achieve is to display the equipment that is only available for the ROOM booking duration
So in effect the query would only display the record for Equip2 because Equip1 is already booked elsewhere for 09:00 10:00 and 11:00 which is within the duration of the room booking and Equip2 is not booked for the room booking duration so Equip2 is available to book.
Here is the query that I am trying to get to work
Any help is much appreciated
Regards Phill
I am trying to write a query that does not display a record according to criteria
The database is a room and equipment booking database the form displays the room details and the equipment that is booked to that room.
This is displayed in a continuous form using text boxes that represent an hours time slot so if the equipment is booked form 09:00 - 12:00 the three text boxes representing the time 09:00 - 12:00 are set to change colour.
This is done using conditional formating so the text boxes need to be bound to a table because it is on a continuous form.
In the form header the room booking details are displayed so for example
Room 1
15/3/2010 09:00 - 12:00 (this is the room booking date and the duration)
So for example the records look something like this
Equip1 09:00 10:00 11:00 00:00 00:00 00:00 00:00 00:00 etc
Equip2 00:00 00:00 00:00 00:00 00:00 14:00 15:00 16:00 etc
So Equip1 is booked for one three hourly slot 09:00 - 12:00 which is within the room booking duration
and Equip2 is booked from 14:00 - 17:00 which is not booked within the room booking duration
So if the field has a value the conditional formating displays that field in red if the field is set to 00:00 then the field is displayed in green meaning the equipment is available.
So what I am trying to achieve is to display the equipment that is only available for the ROOM booking duration
So in effect the query would only display the record for Equip2 because Equip1 is already booked elsewhere for 09:00 10:00 and 11:00 which is within the duration of the room booking and Equip2 is not booked for the room booking duration so Equip2 is available to book.
Here is the query that I am trying to get to work
Code:
SELECT TblAssetLocal.ID, TblAssetLocal.Model, TblAssetLocal.Manufacturer, TblAssetLocal.Category, TblAssetLocal.AssetDetails, TblAssetLocal.Details, TblAssetLocal.Details1, TblAssetLocal.[08:00], TblAssetLocal.[09:00], TblAssetLocal.[10:00], TblAssetLocal.[11:00], TblAssetLocal.[12:00], TblAssetLocal.[13:00], TblAssetLocal.[14:00], TblAssetLocal.[15:00], TblAssetLocal.[16:00], TblAssetLocal.[17:00], TblAssetLocal.[18:00], TblAssetLocal.[19:00], TblAssetLocal.[20:00], TblAssetLocal.[21:00], TblAssetLocal.[22:00], TblAssetLocal.[23:00], TblAssetLocal.[00:00], TblAssetLocal.BookableOnsite, TblAssetLocal.Description, TblAssetLocal.BookEquip, TblAssetLocal.StartTime, TblAssetLocal.EndTime, TblAssetLocal.Machine FROM TblAssetLocal WHERE ((Not (TblAssetLocal.[09:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[09:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[10:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[10:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[11:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[11:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[12:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[12:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[13:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[13:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[14:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[14:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[15:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[15:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[16:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[16:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[17:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[17:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[18:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[18:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[19:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[19:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[20:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[20:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[21:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[21:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[22:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[22:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[23:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[23:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND (Not (TblAssetLocal.[00:00])>=[Forms]![frmEquipBooking]![TxtStartTime] And (TblAssetLocal.[00:00])<[Forms]![frmEquipBooking]![TxtEndTime]) AND ((TblAssetLocal.BookableOnsite)=True) AND ((TblAssetLocal.Machine) Is Null));
Regards Phill