Query for Range to encompass given date

[Mattbro]

Hello all,
Annual leave-I am trying to create a query to show me who is off work on a given date-'DateX'.
I have a table-tblLeave which has;
'LeaveID' as the pk,
StartDate-1st day of leave
EndDate -last day of leave
StaffID.
I have tried querying where if I give a date, if said date falls within the StartDate and EndDate, the data will show. Of course, using quries such as

 
SELECT  *,
DateX >= StartDate And DateX < EndDate AS Active
FROM tblLeave;

gives me a value for all leave events that start before the DateX, and all end dates that finish after DateX....which is just about all leave events on the calendar.


Any ideas?
Many thanks,
Mattbro

[JoeM]

The condition needs to be in a WHERE clause, i.e.

SELECT  *
FROM tblLeave
WHERE DateX Between StartDate And EndDate;

(assuming that DateX, StartDate, and EndDate are all valid date fields).

[Mattbro]

Fantastic. Thanks JoeM