Access 2013 application crashes on Report with "This expression is typed incorrectly, or it is too c

[ApptiusAccessUser]

I have a Microsoft Access 2013 application (was previously an .mdb file that I upgraded to .accdb, but it crashed before the conversion anyway so I didn't think this would impact the situation in anyway) with a Report that uses a Query as it's datasource and the Query works perfectly fine.

The Query takes one user parameter input which is a number from 1 to 8. I can use any of those values and the Query works fine and gives the desired results.

The Report uses this Query as it's datasource and works just fine with values 1 and 2, but for input 3, 4, or 5 the Report crashes with the generic and unhelpful 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. (Error 3071)

This is a customer application that we inherited (that is, we didn't design or build or code this application) as we do some Access work for other customers.

The customer also gave us a copy of this same application back in January 2015 and the Report works fine for all values. The customer has not changed any forms, reports, queries or VBA code as far as we know (and they are not technically proficient users).

So far, we have spent a couple of days on this and we can't figure out what is causing the report to crash. There doesn't seem to be any tools available in Access to trace this error to which field, data value, expression or anything of that nature that is actually causing the crash. Putting breakpoints in the VBA code shows that the application calls the Report_Open Event, but never gets to the Report_Load event before the crash.

I am looking for an Access guru that may help us to diagnose this.

Is there a way to get Microsoft Partner Hours to help us? I could Zip up the actual Access database and send it to a Microsoft Access expert as the file is only 1.8 MB after being Zipped.

Thank you for your help,
Andre

[June7]

I have no idea what Microsoft Partner Hours is.

You can attach db to post and then if anyone is interested in doing so, can analyze.

[ApptiusAccessUser]

I have no idea what Microsoft Partner Hours is.

You can attach db to post and then if anyone is interested in doing so, can analyze.

Microsoft Partner Hours is a support program available to members of the Microsoft Partner Network (which our company is a member of). Each partner has so many technical support hours available to them per year depending on their business with Microsoft or they can purchase additional hours as needed.

This database has a lot of contact information for people and business names, addresses, phone number, emails, etc... that would be very difficult to scrub out and put random data into.

Also, I suspect there is something with the data that is causing this report to crash because as I mentioned, the exact same forms, queries, reports worked back in January 2015 (it may have kept working later than that, but I do have a copy of the database from January 2015 that works fine for all input parameters of the report).

So, I'm thinking that trying to scrub the data in the tables will actually "fix" the crash and that won't really help solve the problem.

I'm very hesitant to post the database as is to this public forum where I think anyone will be able to get it.

Is there a way that I could password protect the ZIP file and then privately send you the password for only you to open it?

Thanks for your quick reply and your help.

Andre

[June7]

Why should you trust me with your data any more than any other member of this forum?

However, if you want to upload to a fileshare site such as Box.com and PM the link to me, I will try to download and look at.

[ApptiusAccessUser]

Why should you trust me with your data any more than any other member of this forum?

However, if you want to upload to a fileshare site such as Box.com and PM the link to me, I will try to download and look at.

Hi June7,

I was in the process of scrubbing the data to be uploaded to you by replacing a few columns for Name, Address, Phone Number, Email and so on. Then I tested the report and it worked just fine.

So, as I suspected, it was some bad data in some records that was causing the report to crash (however, not the Query from running and producing a datasheet view).

I have discovered a way to stop the report from crashing. It was due to some records having NULLs in one of the columns for a few records. The query uses the expression in a calculated column:
Replace([COSC].[Nom],"-","0") to replace all occurrences of the “-“ with a “0” in the column [Nom] of the table [COSC].

However, some records had [Nom] as NULL and this would cause the Replace function to fail, but it wasn’t stopping the Query from running (but it was showing #ERROR# in the datasheet view). It was causing the Report to crash with the error mentioned in the initial posting here.

So thank you for help and I appreciate the service this forum provides.

Cheers,
Andre