CODEWITHSUNDEEP
sqlms-access-2007
rajeev
rajeev

Reputation: 323

Expression is too complex?

I have a report running of the SQL below. Runs fine when the values are hard coded. However when I supply the values through a form that accepts dates I get the error

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplyfying the expression by assigning parts of the expression to variables

In addition, I also get prompted for the Workdate. What is wrong with my approach? 

SELECT Employees.EmployeeId, Employees.EmployeeName, TasksEntries.Project,
TasksEntries.Task, SUM(TimeTracker.WorkHours) AS TotalWorkHours
FROM Employees INNER JOIN (TasksEntries INNER JOIN TimeTracker ON  
(TasksEntries.EmployeeId=TimeTracker.EmployeeId) AND 
(TasksEntries.TaskID=TimeTracker.TaskId)) ON 
(Employees.EmployeeId=TimeTracker.EmployeeId) AND 
(Employees.EmployeeId=TasksEntries.EmployeeId)
 WHERE TimeTracker.WorkDate>="#" & Forms!frmManagerReport!txtMgrRptStartDate 
 & "#" And TimeTracker.WorkDate<="#" & 
 Forms!frmManagerReport!txtMgrRptEndDate & "#"
 GROUP BY Employees.EmployeeId, Employees.EmployeeName, 
 TasksEntries.Project, TasksEntries.Task;

Upvotes: 1

Views: 1013

Answers (2)

Bohemian
Bohemian

Reputation: 425278

You have combined all your join conditions together, which won't work.

Instead, code them with the join to which they belong:

SELECT e.EmployeeId, e.EmployeeName, te.Project, te.Task, SUM(TimeTracker.WorkHours) AS TotalWorkHours
FROM Employees e
JOIN TasksEntries te ON te.EmployeeId = e.EmployeeId
JOIN TimeTracker tt ON tt.EmployeeId = e.EmployeeId
  AND te.TaskID = tt.TaskID
WHERE tt.WorkDate BETWEEN "#" & Forms!frmManagerReport!txtMgrRptStartDate & "#" AND "#" & Forms!frmManagerReport!txtMgrRptEndDate & "#"
GROUP BY e.EmployeeId, e.EmployeeName, te.Project, te.Task;

I also added table aliases and introduced a BETWEEN to make the query more readable.

Upvotes: 0

CindyH
CindyH

Reputation: 3026

I haven't worked in Access for years, but I hope this will help. 1 - bad expression - Catch the built expression in the debugger just before it executes, but after you've collected the values. Then you can examine and run the built string directly in Access. You might be able to see the error better that way. 2 - prompted for Workdate - probably because the query doesn't really have it. Is it case-sensitive? Are Forms!frmManagerReport!txtMgrRptStartDate and Forms!frmManagerReport!txtMgrRptEndDate actually filled in and a valid date?

Upvotes: 0

Related Questions