Getting Error "Too Complex"

[cooh23]

Hi All,

I have built a report based on the following query:

SELECT tblDailyActivities.ActivityDate, tblDailyActivities.EmployeeID, tblDailyActivities.StartTime, tblDailyActivities.EndTime, tblDailyActivities.Description, tblDailyActivities.Quantity, tblDailyActivities.FunctionsID, (([endtime]-[starttime])*1440) AS TotalTime, tblFunctions.FunctionsType, nz(([tblFunctions].[FunctionsType])="Lunch" Or ([tblFunctions].[FunctionsType])="Break - First" Or ([tblFunctions].[FunctionsType])="Break - Second") AS Expr1
FROM tblFunctions LEFT JOIN tblDailyActivities ON tblFunctions.FunctionsID = tblDailyActivities.FunctionsID
WHERE (((tblDailyActivities.ActivityDate) Between [Forms]![frmAllinOneReport]![txtdatefrom] And [Forms]![frmAllinOneReport]![txtdateto]) AND ((nz(([tblFunctions].[FunctionsType])="Lunch" Or ([tblFunctions].[FunctionsType])="Break - First" Or ([tblFunctions].[FunctionsType])="Break - Second"))=False));

This used to work before i had a lot of data, but now i get this error:

"This expression is typed incorrectly or it is too complex to be evaluated"

What should I do?

Thank you,

 

[llkhoutx]

There's just a improperly named field in the query. Delete the left most colum, run, error generated, delete another, run, error generated, etc until the query runs successfully. The last deleted column is the problem.

"TotalTime" doesn't have a table identifier.

 

[cooh23]

There's just a improperly named field in the query. Delete the left most colum, run, error generated, delete another, run, error generated, etc until the query runs successfully. The last deleted column is the problem.

"TotalTime" doesn't have a table identifier.

Hello,

Thank you for looking into this. I reviewed my data... all 12,000 of them. phew... and found out that the Quantity section contained letters since i have it setup as a "Text" and not a "Number" field. I removed the letters and setup the field as a number and now it works fine. But now my eyes hurt.

Thank you,

 

[llkhoutx]

Wait until you get 22 million rows of garbage. The your back will hurt too.

 

[boblarson]

... and found out that the Quantity section contained letters since i have it setup as a "Text" and not a "Number" field.

Another good argument for making sure you use the right datatype when storing information.

 

[llkhoutx]

Generally use text datatypes unless an autonumber. a field used in a calculation, or a memo field.