CODEWITHSUNDEEP
ms-accessreporttype-mismatch
Tarrier
Tarrier

Reputation: 21

MS Access Report give "data type mismatch" error but underlying query does not

I have a vba subroutine that is adding a WHERE clause to a query before a call to open the report that is built on the query. I know that's a bit ridiculous to do this instead of using a parameterized query, but I couldn't get a parameter query to work for some reason when I was building the function on a deadline and this was the quickest solution that I could come up with. Later my exit routine restores the queries original SQL.

That isn't the problem though. The query runs fine, both before and after adding the WHERE clause, which I've confirmed by stepping through the sub and copying and pasting the constructed SQL statement into a new Access query. It runs just fine.

The problem is with the report. It gives me the "data type mismatch in criteria expression" error, this is true whether I run it before the sub adds the WHERE clause or after the WHERE has been added.

This has been working correctly for weeks and then it suddenly blew up yesterday, threw the error and now the report simply won't print anything. It's not an issue with NULL values; it's not a datatype error. It's in the report, somehow.

I thought there might be some corruption in the database, so have compacted it, and even imported all the objects of the database into a new clean database. But the error persists.

Has anyone ever known the "data type mismatch" error to be in the report rather than the query? I have never posted before because I can usually find my answer by searching enough, but this time I am stuck.

Thanks.

Upvotes: 0

Views: 11997

Answers (1)

Tarrier
Tarrier

Reputation: 21

After a good night's sleep, I was able to follow ElectricLlama's advice and track down the error.

One field in the query was calculated and returning nonsense (#Error), and that field was a "Sort By" field in the report. That was the source of the "data type mismatch error" in the report.

So to answer my own question, if you get a "data type mismatch error in criteria expression" error in a report but not a query, check any calculated fields in the report and the query, and check all the group & sort by fields in the report.

Thanks to both of you for your suggestions.

Upvotes: 2

Related Questions