Query Criteria Causing Data Mismatch Error

[jrubenol]

Hi All,



I have an issue where I've defined a new variable in my query in order to test to see if a number is less than or more than another number. The new variable works as expected as long as there isn't any criteria (which I need to have criteria), once I add criteria I get a data mismatch error. This is a real sticking point for my project so hoping to get it solved. Thanks in advance:


Sample Database: http://www.mediafire.com/?9l5w97q90e7j98d


Test Case: qryStandardsTest: Variable GreaterNum. Run without criteria you'll see no errors, add a 0 to criteria and you'll get the data mismatch


Notes: *I've removed some data in order to preserve identity anonymity
also, there is another very similar variable called "TextTest", this works flawlessly regardless of if there is criteria or not


Goal: There are three cases that I need to test and that's why I've had to set up the tables/queries this way:

1. If Comparison =0 means the question is a text question (Yes, No)
2. If Comparison = 1, means that the question response needs to be greater than standards value

3. If Comparison = 2, same as above but less than

[tdecker81]

I'm not sure if it solves your issue but I saved the output from your query to a temp table and then queried that and it works.

[rpeare]

In your query qryDOJStandards you have two fields:

RealStandard
RealValue

Both of these come up as #error when I run them because you're converting a, potentially, text field to an integer which is going to crash and burn.

Are you trying to count the values or do something else with them? If you're trying to only convert it if it's a number and otherwise leave it null your formula would have to be:

RealStandard: IIf(IsNumeric([standardvalue]),CLng([StandardValue]),Null)

and a similar formula for realvalue

If you do this then using criteria in your second query (qryStandardsTest) will work.