Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: Search Query - Between Dates

    Hello all,
    I am currently trying to build this specific query but it seems i am doing something wrong either in my JOINS or in the type of access query should be.
    Never the less , my query have four parameter: 1. Employee Name
    2. Start Date of annual leave , 3. End Date of annual leave and 4. the type of Annual Leave .
    Here is my query :

    SELECT Main.MainID, Employee.EmployeeName, Main.StartDate, Main.EndDate, LeaveType.LeaveType
    FROM Season INNER JOIN (LeaveType INNER JOIN (Employee INNER JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON LeaveType.LeaveID = Main.LeaveID) ON Season.SeasonID = Main.SeasonID
    WHERE (Employee.EmployeeName Like Forms!SearchForm!EmployeName & "*" or Forms!SearchForm!EmployeName Is Null)
    And (LeaveType.LeaveType Like Forms!SearchForm!LeaveType & "*" or Forms!SearchForm!LeaveType Is Null)
    AND (Main.StartDate Between Forms!SearchForm!StartDate & "*" and Forms!SearchForm!StartDate Is Null)
    And (Main.EndDate Between Forms!SearchForm!EndDate & "*" and Forms!SearchForm!EndDate Is Null);

    What is the problem here ? well it works fine when i search for any name or any type of license but i want also to search start and end date and i want results with BETWEEN.
    When i search for a specific Start Date and an End Date i am not getting any between results.
    As i told you i am not sure if is because of my JOIN type or because i should use any different syntax of SQL.
    Any thoughts and help would be greatfull.
    Thanks for your time
    Updated:
    Another try after a research that i've made on the internet i edit it like that :

    SELECT Main.MainID, Employee.EmployeeName, Main.StartDate, Main.EndDate, LeaveType.LeaveType
    FROM Season INNER JOIN (LeaveType INNER JOIN (Employee INNER JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON LeaveType.LeaveID = Main.LeaveID) ON Season.SeasonID = Main.SeasonID
    WHERE (Employee.EmployeeName Like Forms!SearchForm!EmployeName & "*" or Forms!SearchForm!EmployeName Is Null)
    And (LeaveType.LeaveType Like Forms!SearchForm!LeaveType & "*" or Forms!SearchForm!LeaveType Is Null)
    AND ([Main.StartDate] >=Forms!SearchForm!StartDate & "*" and Forms!SearchForm!StartDate Is Null)
    And ([Main.EndDate] <=Forms!SearchForm!EndDate & "*" and Forms!SearchForm!EndDate Is Null);
    You can see some changes for >= and <= but i am getting an error that says : The expressions is typed incorrectly or is to complicated bla bla
    Last edited by AndreasMIS; 09-10-12 at 09:21.

  2. #2
    Join Date
    Sep 2012
    Posts
    6
    After of several try’s I have found the right syntax , it was easier than I thought
    Here is the syntax:

    SELECT Main.MainID, Employee.EmployeeName, Main.StartDate, Main.EndDate, LeaveType.LeaveType
    FROM Season INNER JOIN (LeaveType INNER JOIN (Employee INNER JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON LeaveType.LeaveID = Main.LeaveID) ON Season.SeasonID = Main.SeasonID
    WHERE ([Employee.EmployeeName] Like Forms!SearchForm!EmployeName & "*" or Forms!SearchForm!EmployeName Is Null)
    And ([LeaveType.LeaveType] Like Forms!SearchForm!LeaveType & "*" or Forms!SearchForm!LeaveType Is Null)
    AND ([Main.StartDate]>=Forms!SearchForm!StartDate or Forms!SearchForm!StartDate Is Null)
    And ([Main.EndDate]<=Forms!SearchForm!EndDate or Forms!SearchForm!EndDate Is Null);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •