Query to show available equipment

[Sketchin]

Hi all, I have managed to completely confuse the crap out of myself with what should be a fairly simple query.

First, here is my SQL code:

SELECT DISTINCT [1stqryInstrumentAvailabilityForDates].BOMDescription, tblReservations.DateInReq, tblReservations.DateOutReq
FROM tblReservations RIGHT JOIN 1stqryInstrumentAvailabilityForDates ON tblReservations.ReservationID = [1stqryInstrumentAvailabilityForDates].ReservationID
WHERE (((tblReservations.DateInReq)<[Forms]![FrmInstrumentAvailabilityforDates]![txtDateOutRequested] And (tblReservations.DateInReq)<[Forms]![FrmInstrumentAvailabilityforDates]![txtDateInRequested]) AND ((tblReservations.DateOutReq)<[Forms]![FrmInstrumentAvailabilityforDates]![TxtDateOutRequested]<[Forms]![FrmInstrumentAvailabilityforDates]![txtDateInRequested])) OR (((tblReservations.DateInReq) Is Null));

I have a form with a subform datasheet that displays which equipment is available based on 2 textboxes on the form called txtDateOutRequested and txtDateInRequested.

In my query I have 3 fields, BOMDescription, DateInReq and DateOutReq. In plain english, I would like to return records where :

txtDateOutRequested AND txtDateInRequested don't fall between a current reservation. I also don't need to show past reservations in the query results, just a list of which equipment is available between txtDateOutRequested and txtDateInRequested.

Any help would be greatly appreciated. Don't hesitate to ask for clarification, which I am sure you will need.

 

[pbaldy]

Assuming you have an equipment table listing all equipment, I see two steps. First, a query that finds all equipment reserved during the period. This may help:

http://www.baldyweb.com/OverLap.htm

Second, use the unmatched query wizard to compare the equipment table against that query, which will give you the equipment NOT reserved during the period.

 

[Sketchin]

So, I have this partially working. I can return the records that are reserved during the period, but I can't get the unmatched query to return any results.

I am running the "Unmatched query wizard", selecting my first query that shows violations, choosing BOMID from my reservation table and from my BOM table, but when I run the query I don't get any results. If I search for Non-Null values, I get the same results showing the conflicting dates, but using "Is Null" gives me nothing. Here is the SQL from the Unmatched query wizard:

SELECT [1stQryInstrumentAvailabilityPBALDYMethod].BOMDescription, [1stQryInstrumentAvailabilityPBALDYMethod].BOMNumber
FROM 1stQryInstrumentAvailabilityPBALDYMethod LEFT JOIN tblBOM_Master ON [1stQryInstrumentAvailabilityPBALDYMethod].[BOMNumber] = tblBOM_Master.[BOMID]
WHERE (((tblBOM_Master.BOMID) Is Null));

 

[pbaldy]

I think you reversed the tables when you ran the wizard. The table with all equipment would be the "base" table, as you're looking for items in that table that aren't in the first query. That would make the Is Null criteria on the field in the query. I'd start the wizard again and choose the table first.

 

[Sketchin]

Figured it out with this:

SELECT tblBOM_Master.BOMID, tblBOM_Master.BOMDescription
FROM 1stQryInstrumentAvailabilityPBALDYMethod RIGHT JOIN tblBOM_Master ON [1stQryInstrumentAvailabilityPBALDYMethod].[BOMNumber] = tblBOM_Master.[BOMID]
WHERE ((([1stQryInstrumentAvailabilityPBALDYMethod].BOMDescription) Is Null));

In case you were interested! Thanks again for the help!

 

[Sketchin]

Oh, and yes you are right, I had them reversed!!

 

[pbaldy]

No problem, and I've been immortalized in a query name!!