Working with dates in queries

[Neil_Pattison]

I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:

[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue

What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.

I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!

 

[Jon K]

You can use Between [StartDate] And [EndDate] in the Where Clause of the SQL Statement in query SQL View like this:-

SELECT .............
FROM ...............
WHERE [Enter a date] Between [tblAbsence].[StartDate] And [tblAbsence].[EndDate];


If the parameter is entered in a text box on a form, you can replace [Enter a date] with [Forms]![FormName]![TextBoxName]
.

 

[Neil_Pattison]

Thanks a lot Jon