I'm getting thi error on existing queries

[u156531]

I'm getting this error below on existing MS Access queries. Can someone please explain why this is happening? I've included the SQL to one of the queries.

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)

SELECT CInt(Mid([qrySelectDates].[monthyear],InStr([qrySelectDates].[monthyear],"/")+1,10)) AS [Year], Int((Mid([qrySelectDates].[monthyear],1,InStr([qrySelectDates].[monthyear],"/")-1))) AS [month], qrySelectDates.MonthYear, Nz([Failures],0) AS MaterialFailures, Eval("forms!frmAdmin!cboMaterial.column(2)") AS Population, qryIRSOutagesbyMonth.IRSOutages, Null AS Gate, 0 AS Occurances, 0 AS Computation
FROM (qrySelectDates LEFT JOIN qryFailures ON qrySelectDates.MonthYear = qryFailures.MonthYear) LEFT JOIN qryIRSOutagesbyMonth ON qrySelectDates.MonthYear = qryIRSOutagesbyMonth.MonthYear


ORDER BY CInt(Mid([qrySelectDates].[monthyear],InStr([qrySelectDates].[monthyear],"/")+1,10)), Int((Mid([qrySelectDates].[monthyear],1,InStr([qrySelectDates].[monthyear],"/")-1)));

[CJ_London]

Usually that is down to your data - typically an unhandled null value

[Micron]

Prime suspect for that: conversion functions such as Int, Cint, etc. will fail if passed a null or zls. You may want to have vba construct your sql after it handles nulls.

[u156531]

Can someone show me how to correct the above error in this SQL.

SELECT Count(FailureReport.ID) AS Failures, FailureReport.equipmentclass AS [C, J, orT?], FailureReport.equipmentclass, Month([DateFailure]) & "/" & Year([DateFailure]) AS MonthYear
FROM FailureReport
WHERE (((FailureReport.datefailure)>=[forms]![frmAdmin]![StartDate] And (FailureReport.datefailure)<=[forms]![frmAdmin]![EndDate]))
GROUP BY FailureReport.equipmentclass, Month([DateFailure]) & "/" & Year([DateFailure]), FailureReport.equipmentclass
HAVING (((FailureReport.equipmentclass)=Eval("forms!frmAd min!cboMaterial.column(1)")));

[u156531]

I forgot to mention that if I remove the Having clause I don't get the error.

[madpiet]

HAVING clause is for filtering aggregated data (like SUMS etc).

should be ANDed to the existing WHERE clause:

AND FailureReport.equipmentclass = Forms!frmAdmin!cboMaterial.column(1);

[Micron]

I don't think you can get a combo column in sql without Eval.

[u156531]

If I remove the Having clause the SQL runs without the error. After that I moved it to the where clause and I got the error again. I'm confused as to what is causing that error message or how to resolve it. The SQL used to work at some point in time but not any longer.

[Micron]

I would try replacing this with a valid value to see what happens:
Eval("forms!frmAd min!cboMaterial.column(1)"

[Welshgasman]

Or even concatenate it to see the actual value with a Debug.Print?