Problem with Nested IIF

[Guest]

I am trying to set up a query with a calculated field (let's call it
[NoticeDate]) that contains a number of conditions.

The query includes the following fields: [CurrentTermExp], [30Days'Notice],
[60Days'Notice], [90Days'Notice]

Assuming that [CurrentTermExp] Is Not Null (I've set the query up to filter
the records accordingly), I want the calculated field to turn out the
NoticeDate based on the following:

If the toggle box [90Days'Notice] is checked, then [NoticeDate] is
[CurrentTermExp] minus 3 months.

If the toggle box [60Days'Notice] is checked, then [NoticeDate] is
[CurrentTermExp] minus 2 months.

If the toggle box [30Days'Notice] is checked, OR IF NO TOGGLE BOX HAS BEEN
CHECKED, by default I'd like [NoticeDate] to be [CurrentTermExp] minus 1
month.

This is what I put in the field row of my query:

NoticeDate: IIF([90Days'Notice]=-1, DateSerial([CurrentTermExp]),
Month([CurrentTermExp])-3,Day([CurrentTermExp]), IIF[60Days'Notice]=-1,
DateSerial(Year([CurrentTermExp]), Month([CurrentTermExp])-2,
Day([CurrentTermExp])), DateSerial(Year([CurrentTermExp]),
Month([CurrentTermExp])-1, Day([CurrentTermExp]))))

I keep getting an error message that says I have the wrong number of
arguments. What have I done wrong?

Laura

 

[David S via AccessMonster.com]

NoticeDate: IIF([90Days'Notice]=-1, DateSerial([CurrentTermExp]),
Month([CurrentTermExp])-3,Day([CurrentTermExp]), IIF[60Days'Notice]=-1,
DateSerial(Year([CurrentTermExp]), Month([CurrentTermExp])-2,
Day([CurrentTermExp])), DateSerial(Year([CurrentTermExp]),
Month([CurrentTermExp])-1, Day([CurrentTermExp]))))

I keep getting an error message that says I have the wrong number of
arguments. What have I done wrong?

You were pretty close - firstly, you're got DateSerial([CurrentTermExp]) as
the first iif result - that doesn't have enough arguments, and it's missing
the Year() qualification. Secondly, you're missing the ( for the second IIF.
Try this:

NoticeDate: IIf([90Days'Notice]=-1,DateSerial(Year([CurrentTermExp]),Month(
[CurrentTermExp])-3,Day([CurrentTermExp])),IIf([60Days'Notice]=-1,DateSerial
(Year([CurrentTermExp]),Month([CurrentTermExp])-2,Day([CurrentTermExp])),
DateSerial(Year([CurrentTermExp]),Month([CurrentTermExp])-1,Day(
[CurrentTermExp]))))

 

[Guest]

Thank you so much! It worked like a dream.

NoticeDate: IIF([90Days'Notice]=-1, DateSerial([CurrentTermExp]),
Month([CurrentTermExp])-3,Day([CurrentTermExp]), IIF[60Days'Notice]=-1,
DateSerial(Year([CurrentTermExp]), Month([CurrentTermExp])-2,
Day([CurrentTermExp])), DateSerial(Year([CurrentTermExp]),
Month([CurrentTermExp])-1, Day([CurrentTermExp]))))

I keep getting an error message that says I have the wrong number of
arguments. What have I done wrong?

You were pretty close - firstly, you're got DateSerial([CurrentTermExp]) as
the first iif result - that doesn't have enough arguments, and it's missing
the Year() qualification. Secondly, you're missing the ( for the second IIF.
Try this:

NoticeDate: IIf([90Days'Notice]=-1,DateSerial(Year([CurrentTermExp]),Month(
[CurrentTermExp])-3,Day([CurrentTermExp])),IIf([60Days'Notice]=-1,DateSerial
(Year([CurrentTermExp]),Month([CurrentTermExp])-2,Day([CurrentTermExp])),
DateSerial(Year([CurrentTermExp]),Month([CurrentTermExp])-1,Day(
[CurrentTermExp]))))