By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

arrggg! Query to complex!!!

Fotorat
P: 13
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")))))))))))
Oct 26 '06 #1
Share this Question
Share on Google+
3 Replies


P: 19
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")
))))))))))
Oct 26 '06 #2

NeoPa
Expert Mod 15k+
P: 31,489
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.
Oct 26 '06 #3

NeoPa
Expert Mod 15k+
P: 31,489
Try
Expand|Select|Wrap|Line Numbers
  1. MonthsAgo: '-' & (12+Month(Date())-Month([EFFDT])) Mod 12
Oct 26 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.