CODEWITHSUNDEEP
ms-accesscount
user1958651
user1958651

Reputation: 451

SUM IIF BETWEEN

I have the following code, the purpose is to count the number of employees in each group who have left the company during the given time frames. I am using COUNT IIF and it says the <= symbol after the AND in each bit of the code is wrong. How am I supposed to have it count the date ranges?

    SELECT O867IA_VPJOBCO.JOB_GRP_CD, Count(IIf(O867IA_VJOBHST.REC_EFF_STT_DT>=#1/1/2009# And <= #12/31/2009#,2009,0)) AS 2009, Count(IIf(O867IA_VJOBHST.REC_EFF_STT_DT>=#1/1/2010# And <= #12/31/2010#,2010,0)) AS 2010, COUNT(IIF(O867IA_VJOBHST.REC_EFF_STT_DT >= #1/1/2011# AND <= #12/31/2011#, 2011, 0)) AS 2010, COUNT(IIF(O867IA_VJOBHST.REC_EFF_STT_DT >= #1/1/2012# AND <= #12/31/2012#, 2012, 0)) AS 2012,
FROM (O867IA_VJOBHST INNER JOIN O867IA_VACTRSN ON (O867IA_VJOBHST.EMP_ACN_RSN_TYP_CD = O867IA_VACTRSN.EMP_ACN_RSN_TYP_CD) AND (O867IA_VJOBHST.EMP_ACN_TYP_CD = O867IA_VACTRSN.EMP_ACN_TYP_CD)) INNER JOIN O867IA_VPJOBCO ON O867IA_VJOBHST.JOB_CLS_CD = O867IA_VPJOBCO.JOB_CLS_CD
WHERE (((O867IA_VJOBHST.REC_EFF_STT_DT)>=#1/1/2009# And (O867IA_VJOBHST.REC_EFF_STT_DT)<=#12/31/2012#) AND ((O867IA_VACTRSN.EMP_ACN_TYP_CD)<>"RMT"))
GROUP BY O867IA_VPJOBCO.JOB_GRP_CD
HAVING (((O867IA_VPJOBCO.JOB_GRP_CD)>='72' And (O867IA_VPJOBCO.JOB_GRP_CD)<='94'));

Upvotes: 1

Views: 1376

Answers (2)

Matt Donnan
Matt Donnan

Reputation: 4993

I have taken your first calculated column as an example:

Count(IIf(O867IA_VJOBHST.REC_EFF_STT_DT>=#1/1/2009# And <= #12/31/2009#,2009,0))

I think you need the Sum method rather than Count in these cases, take a look at this:

Sum(IIf([O867IA_VJOBHST].[REC_EFF_STT_DT] BETWEEN #1/1/2009# And #12/31/2009#,1,0))

Upvotes: 1

user166390
user166390

Reputation:

Start with the problematic expression:

O867IA_VJOBHST.REC_EFF_STT_DT>=#1/1/2009# And <= #12/31/2009#

Then simplify it (here I have replaced a1, a2 and b2 for expressions above):

a1 >= a2 And <= b2

Put in parenthesis showing intent (this is not what the parser does, but it does help to identify the issue here):

(a1 >= a2) And (<= b2)

Hmm. Nope, (<= b2) doesn't look right at all .. (Hint: look at the HAVING clause conditional for a valid expression in the form a1 >= a2 And b1 <= b2.)

Also, see the BETWEEN operator for a different/cleaner way of writing this condition.

Upvotes: 1

Related Questions