Case snytax

  • Thread starter Thread starter MilesMonkMingus
  • Start date Start date
M

MilesMonkMingus

Can I use case statement within an Access query?

I have a data item PCPCajCd1 that may contain a value of "FED", "H&W" and/or
"SMI" When it contains "FED", I want to subtotal the corresponding numeric
data within data item PCPCajAm1 as FED_Sum.

This is the syntax that I am using:

SUM(CASE PCPCajCd1 WHEN "FED" THEN PCPCajAm1 ELSE NULL END) AS FED_Sum
 
you're trying to mix VBA and SQL, that won't work. instead, try

Sum(IIf(PCPCajCd1 = "Fed", PCPCajAm1, 0))

btw, your "default" value needs to be zero (0) rather than Null. in at least
some situations, a Null value will stop the addition in a group of records -
easier to play it safe than find out "which situations" the hard way.

hth
 
Back
Top