[SQL] MSAccess Where Clause with Case statement syntax error...

[moskie_work]

Is there something obviously wrong with this where clause?

WHERE
rt_BEHI_BHBH.Index = (
CASE
WHEN  qry_BEHI_values.BHBHValue < (SELECT MIN(Value) FROM rt_BEHI_BHBH) THEN 1
WHEN  qry_BEHI_values.BHBHValue > (SELECT MAX(Value) FROM rt_BEHI_BHBH) THEN 10
ELSE (SELECT Index FROM rt_BEHI_BHBH WHERE Value =  qry_BEHI_values.BHBHValue)
END )

I've been staring at this for a while, and I can't get it right. Access just gives a generic syntax error (missing operator) for the case statement.

[Moskie]

Want to know what the problem was?


Access doesn't support case statements!

Kill me now.

[WildZero]

Originally posted by Moskie
Want to know what the problem was?


Access doesn't support case statements!

Kill me now.

Access's default SQL doesn't support a lot of things.

Will it work as a pass-through query? We do a lot of this where I work when someone needs Access to interact with an outside database. Access tries to format all SQL into Access-deformed-SQL unless you specify that the query is a pass-through query. (Not sure if this will help if your data is in Access as well).

w0