Convert IIF with switch statement to Case

[Bomac8]

I have a column in a query that calculates regsales base on some Id's and dont think my CASE statement is correct.
SUM (LOTOSMIS_ACC_TYPE.sing_ind * LOTOSMIS_RET_DAILY.grs_amn *
iif( gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 ), switch(LOTOSMIS_RET_DAILY.gm_var=1, 0, TRUE, 1), 0)) AS RegSales

What I have tried so far which I dont think is working.
Sum(T.SING_IND * P.GRS_AMN *
CASE
WHEN
P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 )
THEN 1
else 0 end) as RegSales

What am I doing wrong here?

 

[dhookom]

I would model this logic into your data tables. Consider a table with unique values of gm_cd where the records with values 1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 have a value stored that can be used rather than a large IIf() or CASE WHEN.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

What Duane is trying to say (I hope/guess...) - the data belongs in db Tables, not in your code.
Easier to maintain, modify, read, and understand.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy has this correct. Here is a blog post from Jeff Smith about the subject.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016