I am trying to write an Append query that has a math formula. Part of the formula is to look at a text field called LastFullMonth (contains values such as January, February, etc) and do a calculation based on how many months have elapsed since the fiscal year. (the fiscal year begins in October).
To come up with the number of months elapsed since October, I tried creating:
sinceOct: IIf([lastfullmonth]="October",1,IIf([lastfullmonth]="November",2,IIf([lastfullmonth]="December",3,IIf([lastfullmonth]="January",4,IIf([lastfullmonth]="February",5,IIf([lastfullmonth]="March",6,IIf([lastfullmonth]="April",7,IIf([lastfullmonth]="May",8,IIf([lastfullmonth]="June",9,IIf([lastfullmonth]="July",10,IIf([lastfullmonth]="August",11,IIf([lastfullmonth]="September",12))))))))))))
Then, my formula is:
Code: IIf(YTDPlannedCost > 1.25 * (TotalSunkCost* (DevOrEnhance+Legislative)) * ([sinceOct] / 12),"Red",Null)
But when I try to run the Append query, it asks for the parameter value of sinceOct.
I can run a Select query, but not an Append query. I tried replacing [sinceOct] in my formula with the 12 IIf statements above, but then Access said my formula was too complex.
Any ideas?
Thanks!
To come up with the number of months elapsed since October, I tried creating:
sinceOct: IIf([lastfullmonth]="October",1,IIf([lastfullmonth]="November",2,IIf([lastfullmonth]="December",3,IIf([lastfullmonth]="January",4,IIf([lastfullmonth]="February",5,IIf([lastfullmonth]="March",6,IIf([lastfullmonth]="April",7,IIf([lastfullmonth]="May",8,IIf([lastfullmonth]="June",9,IIf([lastfullmonth]="July",10,IIf([lastfullmonth]="August",11,IIf([lastfullmonth]="September",12))))))))))))
Then, my formula is:
Code: IIf(YTDPlannedCost > 1.25 * (TotalSunkCost* (DevOrEnhance+Legislative)) * ([sinceOct] / 12),"Red",Null)
But when I try to run the Append query, it asks for the parameter value of sinceOct.
I can run a Select query, but not an Append query. I tried replacing [sinceOct] in my formula with the 12 IIf statements above, but then Access said my formula was too complex.
Any ideas?
Thanks!