Ms Access - Data type mismatch

[emmim44]

Hi all,
I am not getting the correct data when use my simple ms access query ... I mean lets say I am looking for 2 (which is a number ) within the records... The result returns the sample " 53, 23, 24, 4, 43, 56, 55, 44, 59 "... Why is that ???
FindRouteID is Autonumber and Route_knowledge is text in ms access... Thanks all.

[Oorang]

Please post the query SQL.

[emmim44]

select ID from drivers where instr(Route_knowledge,FindRouteID)

[CreganTur]

It has to do with the requirements of the InStr function.

From Access Help:


Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
Syntax
InStr([start, ]string1, string2[, compare])


because FindRouteID is autonumber, it doesn't match the requirement of being a String datatype.

You can use a Type Conversion Function of CStr(FindRouteID) to change the datatype of FindRouteID to String.

[emmim44]

Does this -CStr(FindRouteID) work on asp file ? ??

[CreganTur]

Does this -CStr(FindRouteID) work on asp file ? ??

That I don't know :whistle:

Try it and see:dunno

NinjaEdit: I should probably clarify that CStr(expression) is for converting datatypes within VBA.

[emmim44]

No it doesnt work... it gives the same result ....

[Oorang]

Try this:
select ID from drivers where Cbool(instr(Cstr([Route_knowledge]),Cstr([FindRouteID]))<>0);

[emmim44]

Still doesnt work... same results