[SQL] MSAccess Where Clause with Case statement syntax error... - Tilted Forum Project Discussion Community

moskie_work · 08-05-2004, 09:01 AM

Is there something obviously wrong with this where clause?

Code:

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 · 08-05-2004, 07:59 PM

Want to know what the problem was?

Access doesn't support case statements!

Kill me now.

WildZero · 08-11-2004, 03:48 AM

Quote:

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

08-05-2004, 09:01 AM	#1 (permalink)
moskie_work Upright	[SQL] MSAccess Where Clause with Case statement syntax error... Is there something obviously wrong with this where clause? Code: 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.

08-05-2004, 07:59 PM	#2 (permalink)
Moskie Pure Chewing Satisfaction Location: can i use bbcode [i]here[/i]?	Want to know what the problem was? Access doesn't support case statements! Kill me now. __________________ Greetings and salutations.