Access Query - Too Complex to be evaluated, a numeric expression may contain too many complicated elements

Question

I have an access database. There is a field that is [Start Date]. The [Start Date] has values of 01/01/2017 and only one that is 07/24/2017. The [Start Date] field is a Short Date field in the table. When I change the 7/24/2017 to 01/01/2017 the query runs fine, however when I have July selected in the drop down and that form value is 7 and the 2017 in the Year drop down the following query says it is too complex to be evaluated. I am trying to use the dateserial function to do a datediff and some division against a value with a decimal. Here is the query:

Base Charge: Round(Sum(IIf(DateDiff("d",[Return to COE Regions].[Start Date] 
,CDate(DateSerial([Forms]![Main]![cboReportYear],[Forms]![Main]!
[cboReportMonth],1)))<30,[Base Charge HAL].[Base Charge]/DateDiff("d",
[Return to COE Regions].[Start Date],CDate(DateSerial([Forms]![Main]!
[cboReportYear],[Forms]![Main]![cboReportMonth],1))),[Base Charge HAL].[Base 
Charge])),2)

The dateserial typically is:

DateSerial(Year(Date),Month(Date),1)

I am trying to put the form drop down values in as the date. I have also tried this:

Base Charge: Round(Sum(IIf(DateDiff("d",[Return to COE Regions].[Start 
Date],format(DateSerial([Forms]![Main]![cboReportYear],[Forms]![Main]!
[cboReportMonth],1,"mm/dd/yy"))))<30,[Base Charge HAL].[Base Charge]/DateDiff("d",
[Return to COE Regions].[Start Date],format(DateSerial([Forms]![Main]!
[cboReportYear],[Forms]![Main]![cboReportMonth],1,"mm/dd/yy")))),[Base Charge HAL].[Base 
Charge])),2)

I have also tried:

Base Charge: Round(Sum(IIf(DateDiff("d",[Return to COE Regions].[Start 
Date],format([Forms]![Main]![cboReportMonth]/1/[Forms]![Main]!
[cboReportYear],"mm/dd/yy"))<30,[Base Charge HAL].[Base 
Charge]/DateDiff("d",
[Return to COE Regions].[Start Date],format(DateSerial([Forms]![Main]!
[cboReportMonth]/1/[Forms]![Main]![cboReportYear],"mm/dd/yy")),[Base 
Charge HAL].[Base 
Charge])),2)

I have also concatenated to try and string it together. The July Value in the month drop down which is 7 won't calculate in that query.

From what I have read there is difficulty in queries with Form Drop Down Values. Is there a work around? Is it suggested that I store the values in a vba variable or run the query in vba instead?

I don't know whether this is the reason for the error, but I know it will cause an error:- in your "also tried" your DateSerial([Forms]![Main]! [cboReportYear],[Forms]![Main]![cboReportMonth],1 is missing its closing bracket, and DateSerial([Forms]![Main]! [cboReportMonth]/1/[Forms]![Main]![cboReportYear] is missing its closing bracket as well as two parameters (it needs year, month and day passed to it - but you are passing one number calculated by two divisions, i.e. something like 8 / 1 / 2017 which is approx 0.003966) — YowE3K
Also, why are you doing CDate(DateSerial(...))? DateSerial is already a Date so there is no point trying to convert it. — YowE3K

Gustav Gustav · Accepted Answer · 2017-09-04T06:39:57

Access don't know what the form expressions may hold, so specify that as parameters.

In SQL:

PARAMETERS
    [Forms]![Main]![cboReportYear] Short,
    [Forms]![Main]![cboReportMonth] Short;
SELECT ... <your query>

And do remove CDate from your expression as already noted.

Access Query - Too Complex to be evaluated, a numeric expression may contain too many complicated elements

1 Answers