9

I am trying to troubleshoot a Report in an MS Access Database. This file was build by the person that held my role before I joined the organization I work for.

The report is throwing an error "Data type mismatch in criteria expression" This leads me to believe that the data being imported into the DB is not compatible with the data types set in the report's query. There are 53 pieces in the SQL SELECT Statement.

Is there a good way to trouble shoot the statement (in Access) to find which piece is causing the error? I'm just getting back into using Access so I'm a little rusty.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
JasonR
  • 399
  • 4
  • 8
  • 26

1 Answers1

8

The criteria expression is the part of the query containing the conditions, as in WHERE <condition>. Look at those specifically. The error message means you're comparing two things (this equals that, or this less than that, etc.) that are of two different, and incompatible types (comparing a number to a string, for example).

You can find out which condition is causing the problem by deleting them one at a time and testing again each time until the error disappears.

Make sure you save a copy of your original query, or make a new copy for testing.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • OK, in my case there is no WHERE clause however the FROM includes an INNER JOIN. I might need to break the JOIN into pieces to find where it goes wrong? – JasonR Jan 26 '15 at 17:56
  • That would be my guess, yes. – Robert Harvey Jan 26 '15 at 17:58
  • 2
    I was just analyzing the tables that are being queried. This particular statement is dealing with ages. It appears that, in addition to regular ages (ie: 45, 28, 17, 80) there are also values like (5Y 11M, 1Y 08M) and since my statement is trying to pull out everyone over 75 it doesn't understand this string. This is why you don't allow free form data fields with out validation! – JasonR Jan 26 '15 at 20:15