MS Access report based on table raises "query is too complex" error

Question

Problem: I have a grouped MS Access 2010 report directly linked to a local table. It has 40 'bound' fields per record, and 152 calculated fields per group. When my vba script tries to automatically update the control source of the last few calculated fields (at runtime) it throws a "Query is too complex" error!

Background: I'm creating an MS Access report directly linked to a local table, which contains a Person field/column, a Project field/column and 38 numeric fields/columns for each record.

The report is grouped on Person, and each person has multiple project records.

In the group footer for each person I have 4 calculated controls beneath each of the 38 numeric fields/columns.

So to recap, I have a total of 40 'bound' / directly linked fields per record and 152 calculated fields per group.

The report is tied directly to a local table that is periodically dropped and recreated by a maketable query that is tied to a dynamic SQL query that outputs different columns depending on today's date. In order to account for the different field names that will be provided by the dynamic query I have some VBA script that does the same calculation as happens on the server (calculates all months between today's date -11 months and +6 months and formats to match expected column names), then changes the control sources of the relevant report fields and column label captions to match what is expected to come through from the table, which comes from the dynamic query.

It all seems to work great, apart from my last few lines of code to update some of the calculated fields in the group footer. Here's the working section of the script (from the On Open event of the report) that all seems to be working fine, just for context:

Dim NowDate As Date
Dim i, monthcalc As Integer
Dim Dates(1 To 18) As String

NowDate = Now()

monthcalc = -11

'The below creates variables of formatted dates to be used as control sources for the expected fields incoming from SQL
For i = 1 To 18
    Dates(i) = Year(DateAdd("m", monthcalc, NowDate)) & Format(month(DateAdd("m", monthcalc, NowDate)), "00")
    monthcalc = monthcalc + 1
Next i


monthcalc = -11

'The below sets the column captions to match the expected fields incoming from SQL
For i = 1 To 18
    Me.Controls("Date" & Trim(Str(i))).Caption = MonthName(month(DateAdd("m", monthcalc, NowDate))) & " " & Year(DateAdd("m", monthcalc, NowDate))
    monthcalc = monthcalc + 1
Next i

'The below sets the dynamic 'bound' numeric fields to match the expected fields incoming from SQL
For i = 1 To 18
    Me.Controls("Date" & Trim(Str(i)) & "P").ControlSource = Dates(i) & " P"
    Me.Controls("Date" & Trim(Str(i)) & "TS").ControlSource = Dates(i) & " TS"
Next i

'The below sets the first calculated column beneath each numeric field
For i = 1 To 18
    Me.Controls("TotalDate" & Trim(Str(i)) & "P").ControlSource = "=Sum([" & Dates(i) & " P])"
    Me.Controls("TotalDate" & Trim(Str(i)) & "TS").ControlSource = "=Sum([" & Dates(i) & " TS])"
Next i

Now the code that is problematic (note three of the lines within the loop are commented out):

For i = 1 To 18
    Me.Controls("TotalPHrsPDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([Project] Like 'AS#*' Or [Project] Like 'ABG#*' Or [Project] Like 'ADG#*' Or [Project] Like 'AE#*' Or [Project] Like 'AI#*' Or [Project] Like 'AIN#*' Or [Project] Like 'AM#*' Or [Project] Like 'IMP#*',[" & Dates(i) & " P]), 0))"
    ' Me.Controls("TotalPHrsTSDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([Project] Like 'AS#*' Or [Project] Like 'ABG#*' Or [Project] Like 'ADG#*' Or [Project] Like 'AE#*' Or [Project] Like 'AI#*' Or [Project] Like 'AIN#*' Or [Project] Like 'AM#*' Or [Project] Like 'IMP#*',[" & Dates(i) & " TS], 0))"
    ' Me.Controls("TotalNPHrsPDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([Project] Not Like 'AS#*' And [Project] Not Like 'ABG#*' And [Project] Not Like 'ADG#*' And [Project] Not Like 'AE#*' And [Project] Not Like 'AI#*' And [Project] Not Like 'AIN#*' And [Project] Not Like 'AM#*' And [Project] Not Like 'IMP#*',[" & Dates(i) & " P],0))"
    ' Me.Controls("TotalNPHrsTSDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([Project] Not Like 'AS#*' And [Project] Not Like 'ABG#*' And [Project] Not Like 'ADG#*' And [Project] Not Like 'AE#*' And [Project] Not Like 'AI#*' And [Project] Not Like 'AIN#*' And [Project] Not Like 'AM#*' And [Project] Not Like 'IMP#*',[" & Dates(i) & " TS],0))"
Next i

I can only have any one of the above lines within the loop uncommented, as soon as I try to run two or more of them it throws a "query is too complex" error when I attempt to open the report.

I have tried running them in separate loops, but the same error is raised. These are the last few lines of code, so it is possible I have hit some kind of limit after one line runs.

Any any suggestions as to a fix?

I have considered moving the group totals & calculations to a subreport, but could I have this included in the main report group footers to only show totals for that group? Would whatever limit that I'm hitting be affected by a subreport?

Answer 1

OK, wow. You have built quite a lot of complexity into your code, and I'm not at all surprised that Access is complaining. Now, you can spend a lot of time tinkering, and eventually find exactly what is throwing the error.

But what you really need to do is simplify.

Take your problem code. Do you really need to sum that complex IIf() statement 72 times over the whole underlying recordset to get your answer? No.

My fix: add a numeric field called ProjectType to your table. As part of your refresh, run a query like so:

UPDATE MyLocalTable 
SET [ProjectType] = IIf([Project] Like 'AS#*' Or [Project] Like 'ABG#*' 
    Or [Project] Like 'ADG#*'  Or [Project] Like 'AE#*' Or [Project] Like 'AI#*' 
    Or [Project] Like 'AIN#*' Or [Project] Like 'AM#*' Or [Project] Like 'IMP#*',1, 0)

Now, you've done two significant things:

• You have typed that awful IIf() statement just once, making your code easier to understand and maintain
• You have calculated the ProjectType for each row exactly once, not 72 times.

Now your problem code looks more like this:

For i = 1 To 18
    Me.Controls("TotalPHrsPDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([ProjectType]=1,[" & Dates(i) & " P]), 0)"
    Me.Controls("TotalPHrsTSDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([ProjectType]=1,[" & Dates(i) & " TS], 0)"
    Me.Controls("TotalNPHrsPDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([ProjectType]=0,[" & Dates(i) & " P],0))"
    Me.Controls("TotalNPHrsTSDate" & Trim(Str(i))).ControlSource = "=Sum(IIf([ProjectType]=0,[" & Dates(i) & " TS],0))"
Next i

Much more succinct. And much more likely not to throw an error. You can take this idea and eliminate a lot more complexity from this code.