Problem running report with large amounts of data 1

[Molby]

Using Access 2000 on XP.

I’ve got a main report which contains 3 sub-reports. The 3 sub-reports are based on nested queries. The report runs okay when I have a small number of records from the main query (around 50) but fails with anything larger than 200 records. It runs for about 30 minutes after which I get the following 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.

Is there anyway around this, or am I pushing the boundaries of what Access is capable of doing in one go? I can’t filter the data anymore, is there anything else I can do?

 

[Crowley16]

yep, sounds like you're trying to push the boundaries...

it's not the fact that there's over 200 records that's giving you the problem, it's the fact that you're trying to make the queries/subqueries/nestedqueries too complicated.

try simplifying the queries themselves, ie, reduce calcs done by the query, nesting queries, reduce fields selected if possible...
and try to use seperate queries if possible, or even seperate the reports...

 

[Molby]

Thanks Crowley,

Unfortunately we cannot reduce the calculations or the number of fields, or split the reports, and each report is based on a separate query. Basically we are trying to replicate (and then improve upon) a mainframe based process using exported text files. Of course it would be far easier to change the process in the mainframe, but that would cost money....

 

[Crowley16]

well, what you can do is to put all your calculated fields on the report itself, and not the query...

if you give us some more details, maybe we can help more...

 

[Molby]

There aren't actually that many calculated fields, just two. I'm going to try and output the results of some of the queries to tables and then produce the data from there.

Unfortunately the structure is too complicated and long winded to explain here, but thanks for the help anyway.

If it comes down to it, the management will just have to fork out some money.

 

[Crowley16]

you could try to use something else apart from access, but that'll probably involve more money as well...

 

[Molby]

Oh yes, trying to get extra applications here involves a month long process of getting the request through 3 different groups to get the new program installed, and yes does require more money.

 

[Crowley16]

yeah, wellcome to the world...

just be glad that you're allowed to have new stuff, I'm not even allowed to create a website... but that's a different story...

 

[MichaelRed]

Another common reason for the specific symptom and error involves using a split db, where the BE is on another machine. Most networks are set up (as default) for general office operations. This 'document centric' set up is based on the typical network traffic requirements (e.g. email, memos, spreadsheets, ... etc). This works well for most offices and is even "OK" for small (simple) database usage. It is entirely UNSUITABLE for complex / large database applications. The process is probably 'timing out' or rejecting the transfer of data back and forth because of conflicts in the read/write status. The "real" soloution is (if the above is corect) is to re-configure the network parameters to accomodate the DATABASE needs. A generic workaround is to include a routine to copy all of the necessary data to local (?emporary?) table(s), generate the report (making sure that you delet the temporarytable , only reference them in the specific report processing, and be sure to regularly compact the db.





MichaelRed
mlred@verizon.net