arrggg! Query to complex!!!

[Fotorat]

I did this code converts a date into a value -1 to -12 to be used in crosstab report headers.

The problems arises when I insert the last IIF statement for month -12!!!

up to -11 it runs and works but I get The Expression you entered is to complexwhen I inseert the last iff for -12. Any ideas???

this is what tips it over:

,IIf(DatePart(" m",[EFFDT])=DatePart("m", (DateAdd("m",-12,Now()))),"-12")

this is where it works:

MonthsAgo: IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-1,Now()))),"-1",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-2,Now()))),"-2",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-3,Now()))),"-3",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-4,Now()))),"-4",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-5,Now()))),"-5",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-6,Now()))),"-6",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-7,Now()))),"-7",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-8,Now()))),"-8",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-9,Now()))),"-9",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-10,Now()))),"-10",
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-11,Now()))),"-11")))))))))) )

[birchw]

Try this...

MonthsAgo: IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-1,Now())),"-1"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-2,Now())),"-2"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-3,Now())),"-3"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-4,Now())),"-4"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-5,Now())),"-5"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-6,Now())),"-6"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-7,Now())),"-7"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-8,Now())),"-8"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-9,Now())),"-9"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-10,Now())),"-10"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-11,Now()))),"-11"),
IIf(DatePart("m ",[EFFDT])=DatePart("m", (DateAdd("m",-12,Now()))),"-12")
))))))))))

[NeoPa]

It's too complex because all the IIfs are nested.
Do you really want columns labelld "-1" to "-12"?
I would have thought "0" to "-11" (current month back till 11 months ago covers the last year) would be more accurate.

[NeoPa]

Try

MonthsAgo: '-' & (12+Month(Date())-Month([EFFDT])) Mod 12