Case statement in Access Query

[Eyespi20]

I'm trying to use the following case statement in a query in Access and am getting a syntax error of "missing operator". Can anyone help me form this correctly?

select distinct db.Main.store_num, db.Main.st, sumoftax_pct = case when db.Main.st = 'VA' then sum(db.taxpct.tax)/2 else sum(db.taxpct.tax) end
group by db.Main.store_num, db.Main.st

Thanks in advance for your help

Margaret

 

[PHV]

Provided you don't use a PassThru query, you may try this:
SELECT db.Main.store_num, db.Main.st, Sum(IIf(db.Main.st='VA', db.taxpct.tax/2, db.taxpct.tax)) As sumoftax_pct
FROM ...
GROUP BY db.Main.store_num, db.Main.st

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Access doesn't support CASE. You need to use IIF

select distinct db.Main.store_num, db.Main.st, 

IIF(db.Main.st = 'VA' , 
   sum(db.taxpct.tax)/2 , 
   sum(db.taxpct.tax)) As sumoftax_pct 

group by db.Main.store_num, db.Main.st

 

[Eyespi20]

Worked like a charm, PHV! Thanks a bunch!

Margaret