iif in combination with type cast

[evaleah]

I have the following bit of MS Access SQL Code:

select * from tbltestdata where iif(isnull(Field),0,CLng(Field)) in (1, 2, 3, 4, 5, 6, 7, 777) and ShortName = "OpType"

When I run it, I get a "Data Type Mismatch" error. Can anyone suggest a way to do this? There are several reasons why the column needs to remain text so changing the column type is not the answer in this case.

Thanks!

 

[HarleyQuinn]

I'm going to say that it's because there is data in your Field that can't be converted to a Long (e.g. actual text).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[HarleyQuinn]

Sorry, an actual solution might have helped as well!

Take out the CLng and make the query something like (to use text rather than numeric):

select * 
from tbltestdata 
where iif(isnull(testField),0,Field) in ("1","2", "3", "4", "5", "6", "7", "777") and ShortName = "OpType"

Hope this helps



HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post