Report created in Access 2003 crashes in Access 2010

[mishash]

Dear forum members, good day!



I am trying to work in Access 2010 with a DB built 9 years ago in Access 2003.

The only incompatibility with forms was #Error in forms with no records due to a failing expression =IIf([Form].[Recordset].[RecordCount] > 0, Sum([Amount]), 0), which I've worked around with this
http://access.mvps.org/access/forms/frm0022.htm

I have 30 quite uncomplicated reports dealing with different aspects of the data. All the reports are further assembled in one super-report (simply incorporated one below another), so that a user may select a client' name and print all the data related to the selected client in one click. The super-report worked perfectly well in Access 2003, but it crashes in Access 2010 ("Access had stopped working" etc) with no apparent error.

I've tried all the usual recoveries - compiling, compacting & repairing, exporting the objects to a new DB, saving the DB as an Access 2010 (.accdb) - to no avail.
I've opened every sub-report one by one and found out that a few of them also crashed every time, although their structure and underlying query' nature are no different from the "healthy" ones. The "bad" sub-reports crashed the application either when trying to print, to open in design view or to open in print view. I've played with the print drivers (Brother print machine, PDF Foxit printer) - to no result (all other applications print fine either to the physical printer or to the Foxit PDF).

After endless tries & errors, by sheer serendipity, I've imported all the reports to a new .accdb database, opened one by one in Layout View (a feature unavailable in .mdb database), only than was I able to open the "bad" sub-reports in Design View. After "curing" all the sub-reports, I did the same with the super-report (opened it in Layout view, than in Design view). Than I've imported them back to my DB. All the sub-reports now work flawlessly in Access 2003 DB too, but the SUPER-report still crashes, even though it was processed the same way as its sub-reports.

My question is:
1) Has anybody encountered the same problem with 2003 DB in Access 2010 (specifically - in reports)?
2) Is it a known problem?

I have a strong feeling that it is not code-related or version-related issue and that the solution should be simple and obvious.

Below is example of the code in one of the "bad" sub-reports (which crashed prior to the "curing"). I know that spaces in the tables' names are not welcomed, yet it worked fine in Access 2003.

SELECT [Investment Type List].InvestmentChannel, [Risk Level List].RiskLevel, Sum([NominalValue]*[Rate]*[CreditDebit]*[CurrencyRate]) AS TotalStockHolding, [Client Details].ClientID, [Risk Level List].RiskLevelID
FROM ([Client Details] INNER JOIN [Account Details] ON [Client Details].ClientID = [Account Details].ClientID) INNER JOIN (([Risk Level List] RIGHT JOIN ([Investment Type List] INNER JOIN ([Currency List] INNER JOIN [Stock Details] ON [Currency List].CurrencyID = [Currency List].CurrencySymbol) ON [Investment Type List].InvestmentTypeID = [Stock Details].InvestmentType) ON [Risk Level List].RiskLevelID = [Stock Details].RiskLevel) INNER JOIN ([SellBuy List] INNER JOIN [Stock Execution] ON [SellBuy List].SellBuyID= [Stock Execution].SellBuyID) ON [Stock Details].StockName = [Stock Execution].StockName ) ON [Account Details].AccountID= [Stock Execution].AccountID
GROUP BY [Investment Type List].InvestmentChannel, [Risk Level List].RiskLevel, [Client Details].ClientID, [Risk Level List].RiskLevelID
HAVING ((([Investment Type List].InvestmentChannel)="Stocks") AND ((Sum([NominalValue]*[Rate]*[CreditDebit]*[CurrencyRate]))<>0));

[CJ_London]

depends on when you are running this

=IIf([Form].[Recordset].[RecordCount]

is it a control source to a 'total' control in the form footer or header? somewhere else?

I don't see a field called 'amount' in your query

your query would be better if this part of your HAVING clause was in a WHERE clause

(([Investment Type List].InvestmentChannel)="Stocks")

As Access matures, it has become less tolerant of poor coding. Spaces are still allowed - it is just bad practice. Use the caption property in the table design to show labels with spaces, that is what it is for.

[mishash]

Dear Ajax, thanks for your reply.

Not sure what you mean:

depends on when you are running this
=IIf([Form].[Recordset].[RecordCount]
is it a control source to a 'total' control in the form footer or header? somewhere else?

Nowhere in the reports have I this code.

Regarding the query, I agree mine is not perfect, but it works right now (after the manipulation with exporting to .accdb, opening in design view and importing back) and the output looks like this:
Risk Level Table
Channel, % Stock Holding, $ Risk Level
87% 550,249 Low
13% 79,000 High