Selecting data between dates RRS feed

  • Question

  • I am using this query to select leave taken by employees between two dates including the start date and end dates but I am not getting any results although I should. This is the query and I will like to know what I am doing wrong:

    PARAMETERS Forms!frmLeaveReturn!txtStartDate DateTime, Forms!frmLeaveReturn!txtEndDate DateTime;
    SELECT tblStaff_1.FirstName, tblStaff_1.LastName, tblStaffLeave.LeaveID, tblStaffLeave.StartDate, tblStaffLeave.Days
    FROM tblStaff AS tblStaff_1 LEFT JOIN (Select tblStaff.StaffID, qryLeaveApplications.LeaveID, qryLeaveApplications.StartDate, qryLeaveApplications.Days FROM tblStaff INNER JOIN qryLeaveApplications ON tblStaff.StaffID = qryLeaveApplications.StaffID WHERE qryLeaveApplications.StartDate BETWEEN [Forms]![frmLeaveReturn]![txtStartDate] AND [Forms]![frmLeaveReturn]![txtEndDate]) AS tblStaffLeave
    ON tblStaff_1.StaffID = tblStaffLeave.StaffID;

    Tuesday, March 20, 2018 8:15 PM

All replies

  • You are quite right to use a subquery on the right side of the LEFT OUTER JOIN, but why do you include the tblStaff table in the subquery?  It is already included in the outer query, so is redundant.  The subquery need only include qryLeaveApplications, from which it can return the StaffID column on which the subquery and outer query are joined.

    Ken Sheridan, Stafford, England

    Tuesday, March 20, 2018 8:55 PM
  • The intention is to have all the names of employees whether they take leave or not. If I don't use tblStaff in the subquery only the names of those who take leave will be shown. My problem seems to be this where clause: 

    WHERE qryLeaveApplications.StartDate BETWEEN [Forms]![frmLeaveReturn]![txtStartDate] AND [Forms]![frmLeaveReturn]![txtEndDate]

    On the other hand I get results if I use a where clause like the following:

    WHERE qryLeaveApplications.StartDate <= [Forms]![frmLeaveReturn]![txtStartDate]

    I want to get all leave taken between two dates but my WHERE clause which uses BETWEEN ... AND is not producing results. Am I using it incorrectly or is there another way to get the required results.

    Wednesday, March 21, 2018 1:13 AM
  • Hi Gordon Swan,

    You had mentioned that,"On the other hand I get results if I use a where clause like the following:"

    I try to create a dummy tables with sample data in it and try to run the exact query.

    Your query has error in it.

    I also made a test with 'between' with the same result.

    Please try to confirm that it is working on your side.

    I also suggest you to provide a table design and sample data.

    So it will help us to make a test.

    If possible for you then you can share a sample database with dummy data in it.

    So that we can quickly made a test and see the result.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 21, 2018 2:43 AM
  • If I don't use tblStaff in the subquery only the names of those who take leave will be shown.

    That's not the case.  The outer query returns the names, not the subquery.  In fact the subquery as it stands will not return those names which don't match the criteria due to the INNER JOIN within the subquery.  The reason for using a subquery is that, by joining the referenced table to it in a LEFT OUTER JOIN, the complete set of names can be returned by the outer query rather than a subset, which would be the case otherwise.  The following is the example I use to illustrate this in my online DatabaseBasics demo:

    SELECT FirstName, LastName, EmployerCount
    FROM Contacts LEFT JOIN
        (SELECT ContactID, COUNT(*) AS EmployerCount
         FROM ContactEmployers
         GROUP BY ContactID
         HAVING COUNT(*) > 1)  AS CE2
    ON Contacts.ContactID = CE2.ContactID
    ORDER BY LastName, FirstName;

    As you can see the Contacts table is joined to the subquery on the foreign key of the ContactEmployers table.  You can do similarly.  This does not explain the behaviour you are experiencing, however.  Two other points to consider are:

    1.  Have you declared the parameters, as in Deepak's example?  Parameters of DateTime data type should always be declared to (a) avoid the parameter being interpreted as an arithmetical expression rather than a date, and (b) allow for regional variations in the date format in which the parameter value is entered.

    2.  A BETWEEN.....AND operation on dates can only be guaranteed to return the correct (or more strictly speaking 'anticipated') results if steps have specifically been taken, by means of a Validation Rule, in the table definition to exclude values with a non-zero time of day element.  If this has not been done, the range should be defined differently:

    WHERE qryLeaveApplications.StartDate >= [Forms]![frmLeaveReturn]![txtStartDate]
    AND qryLeaveApplications.StartDate < [Forms]![frmLeaveReturn]![txtEndDate]+1

    Ken Sheridan, Stafford, England

    Wednesday, March 21, 2018 11:49 AM