create SQL query to search between two dates in existing query (1 Viewer)

[barwood]

Hello I am creating a database for work that allows me to enter maintenance tasks for computers into an access database using forms. I have one main table that holds all of the data re the tasks. Its called "Tasks" and comtains the following fields:

TaskID (primary key)
EmployeeID
Date
Window
Serial #
Problem Category
ProblemSubCategory
Severity
TaskStatus
Comments

This table is then connected to several other sub tables with a 1 to many relationship. these sub tables are: Employees, TaskStatus, Window, Severity, ProblemCategory and ProblemSubCategory.

I use a form to enter a maintenace tasks and have it setup to be able to search for tickets using the employee name (and/or) Severity (and/or) Status. The resulting SQL allows this to work:

SELECT Task.TaskID, Task.Date, Task.[Serial #], Severity.Severity, TaskStatus.TaskStatus, Employees.Employees, Task.Comments
FROM TaskStatus INNER JOIN (Severity INNER JOIN (Employees INNER JOIN Task ON Employees.EmployeeID=Task.EmployeeID) ON Severity.SeverityID=Task.Severity) ON TaskStatus.TaskStatusID=Task.TaskStatus

WHERE ((([Task.EmployeeID]=Forms!TaskAdmin!Combo59 Or forms!TaskAdmin!Combo59 Is Null)=True) And (([Task.Severity]=Forms!TaskAdmin!Combo61 Or Forms!TaskAdmin!Combo61 Is Null)=True) And (([Task.TaskStatus]=Forms!TaskAdmin!Combo71 Or Forms!TaskAdmin!Combo71 Is Null)=True));

This will accept a value from one of the combo boxes or a NULL value in which case it is ignored.

What I want to do is also add some code to the existing SQL to also allow me to search for tickets based on a start date and end date. I tried to use the following code to accomplish this :

OR Between (([Task.Date]=[Forms]![TaskAdmin]![StartDate] Or [Forms]![TaskAdmin]![StartDate] Is Null)=True)) And (([Task.Date]=[forms]![TaskAdmin]![EndDate] Or [Forms]![TaskAdmin]![EndDate] is Null)=True))

However when I attempt to save this, access reports a "syntax error(missing operator) in query expression".

I think I could solve this by making a 2nd query, but then I don't think I could use the date search with the employee search. I want to be able to search by employee (and/or) Status (and/or) Severity (and/or) Start Date and End Date.

I have looked all over the internet in various forums to find an answer here, but have come up short. I know a bit about SQL but an far from being an expert.

Anyway I hope that someone here might be able to help and would appreciate any response.

Any takers ?

 

[barwood]

well, I got the SQL statement to take, without causing an error, but unfortunatly it returns incorrect results
This is the SQL code now :

SELECT Task.TaskID, Task.Date, Task.[Serial #], Severity.Severity, TaskStatus.TaskStatus, Employees.Employees, Task.Comments
FROM TaskStatus INNER JOIN (Severity INNER JOIN (Employees INNER JOIN Task ON Employees.EmployeeID=Task.EmployeeID) ON Severity.SeverityID=Task.Severity) ON TaskStatus.TaskStatusID=Task.TaskStatus
WHERE ((([Task.EmployeeID]=Forms!TaskAdmin!Combo59 Or forms!TaskAdmin!Combo59 Is Null)=True)

And (([Task.Severity]=Forms!TaskAdmin!Combo61 Or Forms!TaskAdmin!Combo61 Is Null)=True)

And (([Task.TaskStatus]=Forms!TaskAdmin!Combo71 Or Forms!TaskAdmin!Combo71 Is Null)=True))

Between (([Task.Date]=Forms!TaskAdmin!StartDate Or Forms!TaskAdmin!StartDate Is Null)=True)

And (([Task.Date]=forms!TaskAdmin!EndDate Or Forms!TaskAdmin!EndDate Is Null)=True);


I have included the following fields from the Tasks table:

Date Severity TaskStatus Employee
24/08/2011 Normal Pending Gusti M
30/08/2011 High In Progress Mike R
06/09/2011 Low Pending Graham M
18/07/2011 Mission Critical In Progress Gusti M
26/07/2011 Low In Progress Mike R
27/07/2011 Normal Pending Gusti M
28/07/2011 Normal Pending Gusti M

Now using the SQL that I have so far I can search on either Severity (and/or) TaskStatus (and/or) Employee and the results come out fine.

However if I add the start and end date, the results are incorrect.

If I search on just "Normal" for instance, I get the following results:

TaskID Date Serial # Severity TaskStatus Employees
112 24/08/2011 DT6789R57 Normal Pending Gusti M
117 27/07/2011 HY89GN0303 Normal Pending Gusti M
118 28/07/2011 GH7847369P Normal Pending Gusti M

However If I add the start and end date of July 25 - July 29th, I SHOULD get the last two results (I'm still also searching on "normal" as well).

This is what I get instead:

TaskID Date Serial # Severity TaskStatus Employees
113 30/08/2011 DTY7907F5 High In Progress Mike R
114 06/09/2011 RT6790VT5 Low Pending Graham M
115 18/07/2011 DT6789R57 Mission Criti In Progress Gusti M
116 26/07/2011 FR90784T03 Low In Progress Mike R

The dates are incorrect and it did'nt find any tickets with a severity of "normal"

What am I doing wrong ?

 

[barwood]

I solved it myself. I just used >= and <= for the date query. Works like a charm

SELECT Task.TaskID, Task.Date, Task.[Serial #], Severity.Severity, TaskStatus.TaskStatus, Employees.Employees, Task.Comments
FROM TaskStatus INNER JOIN (Severity INNER JOIN (Employees INNER JOIN Task ON Employees.EmployeeID=Task.EmployeeID) ON Severity.SeverityID=Task.Severity) ON TaskStatus.TaskStatusID=Task.TaskStatus
WHERE ((([Task.EmployeeID]=Forms!TaskAdmin!Combo59 Or forms!TaskAdmin!Combo59 Is Null)=True)
And (([Task.Severity]=Forms!TaskAdmin!Combo61 Or Forms!TaskAdmin!Combo61 Is Null)=True)
And (([Task.TaskStatus]=Forms!TaskAdmin!Combo71 Or Forms!TaskAdmin!Combo71 Is Null)=True))
And (([Task.Date]>=Forms!TaskAdmin!StartDate Or Forms!TaskAdmin!StartDate Is Null)=True)
And (([Task.Date]<=Forms!TaskAdmin!EndDate Or Forms!TaskAdmin!EndDate Is Null)=True);