Access query too complex to be evaluated and a workaround

This post shows how to identify and workaround a limitation or bug in Access.

This is the error message when opening a specific query directly within Access 2003. Using Access 2007 on the other hand seems to be able to handle this type of query.

"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."

I could also get this message in some cases when using the Access Runtime 2007 (using the file extension .accdr). It seems to happen if Access 2003 is installed and Access Runtime 2007 at the same time.

"Execution of this application has stopped due to a run-time error. The application can't continue and will be shut down."

If I use VBA code like this to open nothing happens at all.

DoCmd.OpenQuery "qryInvoice", acNormal, acEdit

Here is a simplified version of the query when it does not work. The original query was 10 times larger and I removed one thing at a time to find out the cause. I have several checkboxes on a form so it's possible to dynamically query based on selected weekdays.

SELECT invoice
FROM invoices
WHERE (((Weekday([DateTimeField],2)) In (-1*[Forms]![frmInvoices]![CheckMo].[value],-2*[Forms]![frmInvoices]![CheckTu].[value],-3*[Forms]![frmInvoices]![CheckWe].[value],-4*[Forms]![frmInvoices]![CheckTh].[value])));

Removing one of the In list of expressions and it starts working as expected.

SELECT invoice
FROM invoices
WHERE (((Weekday([DateTimeField],2)) In (-1*[Forms]![frmInvoices]![CheckMo].[value],-2*[Forms]![frmInvoices]![CheckTu].[value],-3*[Forms]![frmInvoices]![CheckWe].[value])));

I then tried to use constants in the In operator and it did work.

SELECT invoice
FROM invoices
WHERE (((Weekday([DateTimeField],2)) In (1,2,3,4)));

So I ended up with this workaround by using VBA to build the SQL query string and then the query definition and dynamically so to say updating the saved query. This shows also how an Access query can be created using VBA.

On Error Resume Next
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Set dbs = CurrentDb
strQueryName = "qryInvoice"
dbs.QueryDefs.Delete strQueryName
strSQL = "SELECT invoice FROM invoices " & _
"WHERE (((Weekday([DateTimeField],2)) In (" & -1 * IIf(CheckMo.Value, -1, 0) & "," & -2 * IIf(CheckTu.Value, -1, 0) & "," & -3 * IIf(CheckWe.Value, -1, 0) & "," & -4 * IIf(CheckTh.Value, -1, 0) & ")));"
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
DoCmd.OpenQuery strQueryName, acNormal, acEdit