Query error:Data Type mismatch in criteria expression

[mfaqueiroz]

Hey everybody!
I have a derived column showing the # of times a "X" appears within the CodeMachine string. I'm using the follow code:

Count: LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X",""))


i only want the registers with the count="2" but when i write in criteria "="2" or "2" or "like 2" appears this error: Data Type mismatch in criteria expression

Could someone help me?

thank you!

 

[sonic8]

Count: LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X",""))

i only want the registers with the count="2" but when i write in criteria "="2" or "2" or "like 2" appears this error: Data Type mismatch in criteria expression

The result of you calculation is a numeric value. You can not use the LIKE operator and you do need to use delimiters for the expression.

... WHERE LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X","")) = 2

or , if the actual calculation is in a base query:

... WHERE yourCalcColumnAlias = 2

If the column CodeMachine is nullable you should consider using the NZ-Function to avoid errors resulting from NULL values in the table.

 

[Minty]

Also calling your calculated field Count is likely to lead you to some issues as Count is a reserved word / operator in Access.

 

[arnelgp]

cast it to long or integer:

Count: CLng(LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X","")))

 

[mfaqueiroz]

Thanks
sorry i'm not really god on access...
where should i put the "where" condiction?

CountX2: LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X","")) where LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X",""))=2

or i should put " where LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X",""))=2" on criteria?
i tried the two ways but always appears this error:

"The expression you enetered contaisn invalid syntax"

 

[arnelgp]

in query design, put it under the Criteria. since it is already cast as Long, no need to enclosed it in double qoute, ie:

Field: CountX2: CLng(LEN(CodeMachine) - LEN(REPLACE(CodeMachine,"X","")))
Criteria: 2

 

[mfaqueiroz]

Hey Arnelgp! Thanks..I've tried as you suggested but it still appearing the same error "data type mismatch in criteria expression"
Thanks!

 

[arnelgp]

if there are Null values on your CodeMachine field, it will definitely fail. try adding an empty string on the expression:

Field: CountX2: CLng(LEN("" & [CodeMachine]) - LEN(REPLACE("" & [CodeMachine],"X","")))
Criteria: 2

 

[mfaqueiroz]

Thanks arnelpg!
it is solved!!

 

[arnelgp]

nice smile you have there!