Microsoft Access Error - This expression is typed incorrectly or it is too complex to be evaluated
Posted: 2/14/2010 11:46:45 AM
Recently, I was working on a client project in Microsoft Access and
got this error message - "
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.". The error message came up when I was trying to print
a report, which, came up great and had no problems whatsoever, until I tried to
print it.
The help for the error message indicated there might be a syntax
error in an expression in the report. I looked, and looked, and looked for any particular problems the
report could be having, including syntax errors as indicated by the error message
help, but found none. I
looked online for answers and none of the websites seemed to have the answer.
To give you a little more detail on the report I was working with,
and the problem Microsoft Access was probably having with it, here we go. The
report I built had two levels of grouping, data on the headers to show
information for each of the groups, as well as a details section which included
data from a subreport.
The 'funny' thing about all this is the report came up perfectly
fine and great in preview mode. But, the problem only arose when I wanted to
print out the report's details. Then, Microsoft Access got too stupid to read the
details that I had in the report that was already right in front of me, at least
when it comes to printing it out.
Unfortunately, over the years I have seen problems like this with
Microsoft Access. Microsoft Access is fairly capable in building database
applications but can quickly run into limitations when you try to do anything of
much complexity on some things, including this, and reports, and some standard
SQL statements, that don't work in Access, but you know in the real world
they do, like in Microsoft SQL Server.
So, after a bit of frustration I came up with a solution. The
answer was to dumb down the report to a level that even Microsoft Access can
understand. So, first, I split up the results into 2 tables meant to just hold
the data for the report, one for the top two groupings the other for the
subreport in the detail section. Originally, I had used two Microsoft queries to
derive the data for the report, which came from three tables. I gave my two Microsoft Access tables built just
to hold report data solution a try in the report, but no luck. It complained the
same way with the same problem it had given me before.
This is a pain. With
Microsoft Report Viewer I would have none of these problems. But as the
project is in Microsoft Access that's the environment I have to develop this
report, so, like any other developer, I've got to make it work.
So, my quest for dumbing down the report for Microsoft Access
continued. I created a new table specifically for the reports data and built
it's data with one pass of SQL and left a placeholder column for the
grouping data. Then, with a SQL UPDATE statement on the table,
referencing itself, I tried to update the rows grouping column, but, Microsoft Accesses SQL
handling wasn't up to the challenge of the self referencing table update
statement. That's frustrating. So, I turned to going through each of the rows of
the table and updating their records with a SQL UPDATE statement in code while having
the results already predetermined from a recordset. The code loop took care of
it, but simply, a SQL UPDATE statement should have taken care of this by itself.
Microsoft SQL Server would never have a problem with this, or any decent
database system. But I wasn't working there, but working on Microsoft Access. Oh
well.
So, then, with another table built to hold all the data for the
report, including the two parent groupings, as well as the details in the
subreport, I removed the subreport and replaced it's information in the detail
section from the new all-encompassing table for the report. Now I tried my
newest offering to the Microsoft Access gods. Thankfully, they finally felt
sorry for me and the report was allowed to print fine.
Again, with
Microsoft Report
Viewer, I would've been done with this in the first version of the report at
the start of the story. But, this is Microsoft Access, and not everything goes
as it should.
The moral of the story is, if you run into this error message while
trying to print out a report in Microsoft Access, "
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.", it may not be
you, but simply Microsoft Access being dumb. I strongly recommend you do
everything you can to make the data for the report in as simplistic a form as
possible for Microsoft Access -- ideally, put all the report data in one table.
Yes, this is ridiculous, but, it is Microsoft Access. Perhaps some of the moves I
used above can be useful for you to arrive at this one table solution. I
sincerely hope so.
However, if you find yourself unable, unwilling, or basically fed
up and can't find the answer and need help, we are here for you at C Solutions.
Contact us
so we can help get you unstuck on this problem or any other application or
database needs that may arise for you. Have a great day.
Comments