Nested IIF Statement

[reddy]

I need to accomplish the following. I need to count the number of loans by
Lender that fall under three categories: FlatCancel, PartialCancel & Inforce.
Here is a sample of the data

Lender Loan# EffectiveDt CancelDt
BLC 12 1/1/2008 1/1/2008
BLC 45 1/1/2008 4/03/2008
BLC 65 1/1/2008 Null

If the EffectiveDt=CancelDt then "FlatCancel" (Row1)
If the EffectiveDt<>CancelDt AND CancelDt is NotNull then "PartialCancel"
(Row2)
If the EffectiveDt is Null then "Inforce" (Row3)

The query should return:
Lender, IssueMth , IssueYr , #FlatCancels ,#PartialCancels, #Inforce

 

[Douglas J. Steele]

Assuming that IssueMth and IssueYr are based on EffectiveDt:

SELECT Lender, Month([EffectiveDt]), Year([EffectiveDt]),
SUM(IIf([EffectiveDt] = [CancelDt], 1, 0) AS [#FlatCancels],
SUM(IIf([EffectiveDt] <> Nz([CancelDt], [EffectiveDt]), 1, 0) AS
[#PartialCancels],
SUM(IIf(IsNull([CancelDt]), 1, 0) AS [#Inforce]
GROUP BY Lender, Month([EffectiveDt]), Year([EffectiveDt])

(I assume you made a typo in your definition of Inforce, since your
description doesn't match row 3 of your example)

 

[KARL DEWEY]

I think you mean 'If the CancelDt is Null then "Inforce".'
Try this --
SELECT reddy.Lender, Format([EffectiveDt],"mmmm") AS IssueMth,
Format([EffectiveDt],"yyyy") AS IssueYr,
Sum(IIf([EffectiveDt]=[CancelDt],1,0)) AS FlatCancel,
Sum(IIf([EffectiveDt]<>[CancelDt] And [CancelDt] Is Not Null,1,0)) AS
PartialCancel, Sum(IIf([CancelDt] Is Null,1,0)) AS Inforce
FROM reddy
GROUP BY reddy.Lender, Format([EffectiveDt],"mmmm"),
Format([EffectiveDt],"yyyy");

 

[reddy]

Thank you . That worked! How can I add a column that computes an age group -
categorizing the difference between 2 date fields into 0-30,31-60, >60.

I am trying the following and it does'nt work:

Iif([Date1]-[Date2] between 0 and 30,"0-30",
IIF(([Date1]-[Date2] between 31 and 60,"31-60",">60"))

Please help!