Re: Access query to return items free between two user input dates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I realized AFTER I posted that the solution I proposed will only find resources if they have been used at least once.

I think you could use the basic query as a subquery in a where clause
SELECT Y.*
FROM YourTable as Y
WHERE Y.ResourceID NOT IN
(
SELECT Y2.ResourceID
FROM YourTable AS Y2
WHERE Y2.StartDate < = [Enter End Date]
And Y2.EndDate >= [Enter End Date])

Or build an unmatched query using your table and a saved query that is the where clause above.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John Spencer wrote:
The simplest way I can think of is the following. Test it and see if it gives your the desired results.

Parameters [Enter Begin Date] DateTime, [Enter End Date] DateTime;
SELECT Y.*
FROM YourTable AS Y
WHERE Not(Y.StartDate < = [Enter End Date]
And Y.EndDate >= [Enter End Date])


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


angel75 wrote:
Hi, I am setting up loans database with a table holding all the dates that items are going in/out. I want to be able to ask user for the dates and search the table for an item which is free between the two dates.

eg phone 2 is booked on 1/11, so when I enter 1/11 as start and end date I want it to bring back phone 1/3/4. It is excluding the one entry where phone 2 is booked on that day, although it brings back all other entries for phone 2, and its also bringing back phone 1/3/4 future bookings, which is correct.

I am using Not Between StartDate and EndDate. What else do I need to do to ensure if its checked phone 1 and its booked to exclude that phone altogether? I am not good with SQL so want to keep it simple if possible.


SchedId ResourceId StartDate EndDate User Booked CheckedOut Completed
1 PHONE1 15-Jan-08 15-Jan-08 Sarah yes
2 PHONE1 10-Sep-08 12-Sep-08 Vicki yes
5 PHONE2 13-Oct-08 14-Oct-08 Fred yes
4 PHONE2 17-Oct-08 17-Oct-08 John yes yes no
3 PHONE1 20-Oct-08 20-Oct-08 Steve yes no
15 PHONE2 01-Nov-08 16-Nov-08 Jack yes no
11 PHONE2 19-Nov-08 25-Nov-08 Steve yes no
7 PHONE1 22-Nov-08 22-Nov-08 John yes no
17 PHONE1 01-Dec-08 01-Dec-08 Jan yes no

Any help greatly appreciated!
.

Relevant Pages

  • Re: Corstabquery or normal query
    ... This is my SQL for Query: ... "John Spencer" wrote: ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Corstabquery or normal query
    ... "John Spencer" wrote: ... Try to run the query. ... IF you have problems with the crosstab query, ... 2007 Deacon Bob 1 ...
    (microsoft.public.access.reports)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Dim strOut As Variant ... For intCount = 1 To Len ... When I attempt to do the Update Query where I copy and past your code into ...
    (microsoft.public.access.queries)
  • Re: Using If expressions with an Or expression, and a Count questi
    ... The addition of the commas inside the was to ensure exact matches to Bill and Steve and preclude a record with STE being returned. ... "John Spencer" wrote: ... You did not give us any table or field names so what I wrote was a generic example of a query. ... To do this in design view, you would put the IIF statement into a field "box" and the Like into a criteria box under the IIF. ...
    (microsoft.public.access.queries)
  • Re: Removing Formatting Symbols from Phone numbers
    ... "John Spencer" wrote: ... Sometimes the Design View (query grid) will insert quotes in the update to ... Does Access add quote marks around the table and field names or around ... Dim strOut As Variant ...
    (microsoft.public.access.queries)