Append query

[rutica]

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!

 

[DJkarl]

Rather than all those nested IIF statements you might want to look at using the Switch statement

Switch([lastfullmonth]="October",1,[lastfullmonth]="November",2, etc...)

 

[rutica]

I didn't know about the Switch Function. Thanks!

I typed:
Switch([Project Status Survey Budget]![lastfullmonth]="October",1,[Project Status Survey Budget]![lastfullmonth]="November",2,[Project Status Survey Budget]![lastfullmonth]="December",3,[Project Status Survey Budget]![lastfullmonth]="January",4,[Project Status Survey Budget]![lastfullmonth]="February",5,[Project Status Survey Budget]![lastfullmonth]="March",6,[Project Status Survey Budget]![lastfullmonth]="April",7,[Project Status Survey Budget]![lastfullmonth]="May",8,[Project Status Survey Budget]![lastfullmonth]="June",9,[Project Status Survey Budget]![lastfullmonth]="July",10,[Project Status Survey Budget]![lastfullmonth]="August",11,[Project Status Survey Budget]![lastfullmonth]="September",12)

straight into my formula and it worked! It wasn't too complex for Access.

I wonder if there is a more elegant way to do this by somehow using the Month () function... Just wondering.

But ,it's working now thanks to you.

 

[rutica]

I received advice today from someone who gave me the following formula that can be used instead of the nested IIF statements or the Switch statement:

Fiscmonth: IIf(DatePart("m",DateValue([LastFullMonth] & " 1, 1900"))<10,DatePart("m",DateValue([LastFullMonth] & " 1, 1900"))+3,DatePart("m",DateValue([LastFullMonth] & " 1, 1900"))-9)

Interesting. Just wanted to share.

Thanks again for your help.