Help with Error (3071)

[ron3rbns]

Hello!



I am receiving the following error message on a query from a DB that has remained practically unchanged:

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 simplifying the expression by assigning parts of the expression to variables. (Error 3071)

I've never gotten this error before as this message has just recently started appearing within the last week or two. Also, the design of the query has remained UNCHANGED which is baffling. Any insight on this would be ENORMOUSLY appreciated!!!! SQL statement of the query is posted below... Thanks again!!!!

SELECT DISTINCT iheat_CallLog.CallID, iheat_CallLog.RecvdDate, tblCustomerSatisfactionSurveys.Sent AS AlreadySent, [qryCustomerSurveys-CallersThisMonth].Sent AS UserSurveyedThisMonth, DateValue([ClosedDate]) AS ClosedD, iheat_CallLog.CustID, iheat_Profile.FirstName, iheat_Profile.LastName, [tblHRHub-Field-Personnel-archive].FIRST_NAME, [tblHRHub-Field-Personnel-archive].LAST_NAME, [tblCustID-Email].Email, iheat_Profile.ADOfficeCode, iheat_Profile.DivFieldOffCode, iheat_CallLog.Category, iheat_CallLog.CallDesc
FROM ((tblCustomerSatisfactionSurveys RIGHT JOIN ((iheat_CallLog LEFT JOIN iheat_Profile ON iheat_CallLog.CustID = iheat_Profile.CustID) LEFT JOIN [qryCustomerSurveys-CallersThisMonth] ON iheat_CallLog.CustID = [qryCustomerSurveys-CallersThisMonth].CustID) ON tblCustomerSatisfactionSurveys.CallID = iheat_CallLog.CallID) LEFT JOIN [tblCustID-Email] ON iheat_CallLog.CustID = [tblCustID-Email].CustID) LEFT JOIN ([tblEMPLID-EMail] LEFT JOIN [tblHRHub-Field-Personnel-archive] ON [tblEMPLID-EMail].EMPLID = [tblHRHub-Field-Personnel-archive].EMPLID) ON [tblCustID-Email].Email = [tblEMPLID-EMail].EMAIL_ADDR
WHERE (((DateValue([ClosedDate]))>=DateValue([START DATE]) And (DateValue([ClosedDate]))<=DateValue([STOP DATE])) AND ((iheat_CallLog.CallType)="TOPS Gen." Or (iheat_CallLog.CallType)="TOPS EFS") AND ((iheat_CallLog.ClosedDate)>"1"))
ORDER BY DateValue([ClosedDate]) DESC;

[pkstormy]

The line of the syntax could be increasing in length due to values and either is too long for the SQL String to handle length wise, or the values themselves are causing problems with the construction of the links or calculations. Check which values that are being parsed with it by running it as a query in query design versus in vba. Or if in the query design, try simplifying. Look for text fields with lengthy text or date values that return nulls, etc....


If in vba, you can add a:

msgbox "My SQL: " & strSQL (or whatever variable it holds)