reservation trouble

[ice-9]

Ill try excplain.
Im desiging a reservation system. You can book a place for a periode of time.
I have a combobox on a form which displays the free spots left after looking at the formbegindate and forumenddate that the user filled in.

Here is the problem
When a spot has been reserved more then once it does get in the list were it not shoud be. For example:
i want to place a reservation for 1-9-2004 to 1-10-2004.
Now the query starts looking. It sees:
Spot 4 is allready been reserved for 4-9-2004 tot 8-9-2004 so it returns a false and doenst show in the list (so far so good)
But spot 4 had also been reservated for 1-1-2004 to 10-1-2004. This date does not compete with the reservationdate and the query returns a TRUE and it DOES show in the combolist, but als you notice, it was been reserved for 4-9-2004 tot 8-9-2004 so this is wrong.

My question:
Its there a way to solve this?
Is there a way that i can say conform the example: if spot 4 is taken then not show

Here is the query
SELECT Locatie.Soort, Locatie.Elektra, Locatie.Locatiecode
FROM Locatie LEFT JOIN Reservering ON Locatie.Locatiecode = Reservering.Locatiecode
WHERE (((Reservering.Begindatum)>=[Formulieren]![Reserveren]![Begindatum]) AND (([Formulieren]![Reserveren]![Einddatum])<=[Begindatum])) OR ((([Formulieren]![Reserveren]![Einddatum])>=[Einddatum]) AND ((Reservering.Einddatum)<=[Formulieren]![Reserveren]![Begindatum])) OR (((Locatie.Locatiecode) Not In ([reservering].[locatiecode])))
ORDER BY Locatie.Locatiecode;

thanks in advance
joost

 

[ice-9]

to difficult ?

 

[Jon K]

You can split the query into a series of two and use the second query for the Row Source of the combo box. See the attached database, which contains these two queries.

qry1:
SELECT DISTINCT [Reservering].[locatiecode]
FROM Reservering
WHERE [Reservering].[Begindatum]<=[forms]![Reserveren]![Einddatum] And [Reservering].[Einddatum]>=[Forms]![Reserveren]![Begindatum];

qry2:
SELECT [Locatie].[Locatiecode], [Locatie].[Soort], [Locatie].[Elektra]
FROM Locatie LEFT JOIN qry1 ON [Locatie].[Locatiecode]=[qry1].[locatiecode]
WHERE [qry1].[locatiecode] Is Null
ORDER BY [Locatie].[Locatiecode];

The database contains your sample data. qry1 returns locatiecode 4. qry2 will exclude this locatiecode and returns the available locatiecodes 1,2,3,5 for the combo box.

(Note. In order to test the correctness of qry1, the date ranges entered in table Reservering were deliberately repeated. qry1 should still return Locatiecode 4 even when you delete the 3rd to the 7th records from the table to match your sample data.)

The database is in English, so [forms] is used instead of [Formulieren]. Hope you can open it on your system.