Windows MS Access query help needed

I need to make a query that displays pitches at a campsite that are not booked for a given date the user enters.




I have a tbl which holds pitch info tblPitch and a table which holds booking info tblBooking (see attachment)

tblBooking has the pitch number, start date and end date so if theres a date like 1/11/2003 - 7/11/2003

if the user enters a date on or between then i dont want that pitch to be displayed but all pitches that arnt booked to be shown...

im not sure if this makes much sense but if someone gets it please help im new to this.

Heres what ive been trying

SELECT DISTINCT [tblpitch].[siteNo], [tblpitch].[pitchNo]
FROM tblpitch, tblBooking
WHERE tblpitch.pitchNo = tblbooking.pitchNo

And tblBooking.bookingDate between [Enter Date]

and tblbooking.pitchNo NOT IN (select pitchNo from tblBooking)
ORDER BY [tblpitch].[siteNo];


but its wrong as i have no end date or anything.


Thank you for any help.