By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Query Criteria

P: 121

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM TblAssetLocal
  3. 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
Mar 18 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.