Error 3464 (Data type mismatch in criteria expression) with OpenRecordset

[TOPSie]

This is driving me nuts. I have a table with a lot of entries. All the fields are strings (ShortText).

I perform the following

wSQL = "SELECT STNumbers.* FROM STNumbers WHERE STNumbers.STN_Class =""" & wClass & """ ORDER BY VAL(STNumbers.STN_N02) ASC;"
Set rs = CurrentDb.OpenRecordset(wSQL, DB_OPEN_DYNASET)

wClass is atring. And this code works almost everytime for many values I place in wClass. But if I put "LN:A2" I get this error 3464.

I have made sure that every record with this field (STN_Class = LN:A2) has valid string data - but no matter how I try and cleanse this data it always fails - just for this value - all other values work????

[Micron]

Are you sure it's not failing on a record with Null in the field? Only takes one.
FWIW I use " ' " (spaced for clarity) not """ thus don't have to worry about when it works and when it doesn't.

[Welshgasman]

Works for me, in that I have no data to match that criteria on first test?
Works when I add a record to match that criteria.
Works when I have a Null in that criteria field?

SELECT * FROM tblProducts WHERE tblProducts.Product ="LN:A2" ORDER BY VAL(IDProduct) ASC;

[Micron]

SELECT * FROM tblProducts WHERE tblProducts.Product ="LN:A2" ORDER BY VAL(IDProduct) ASC;

I don't see that as being the same thing. Methinks that as posted, with null being passed to the variable, you'd be using a zls as criteria (""). However, after reconsidering, this is a SELECT sql statement, which means zls should not be an issue. I'm now thinking that the problem is about what's being passed to the Val function. If using an error trap, code can be halted when it errs and the value of VAL(STNumbers.STN_N02) can be examined. A string with no numbers should return zero (0). Val(Null) should raise an error.

[Gicu]

For the records with STN_Class = LN:A2 make sure the STNumbers.STN_N02 field is also valid (as the error might come from the Val() function).
EDIT: Micron said it first
Cheers,