Query Criteria

[phill86]

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

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));

Any help is much appreciated

Regards Phill