IIf function 1

[joyced]

I am relatively new to Access but . . . I have created a report using the IIf function and want to create a grand total in the page footer from the expression using the IIf function. Following is the expression:
=IIf([ReimbursementCode]="1",[TotalCreditHrs]*18.61,IIf([ReimbursementCode]="2",[TotalCreditHrs]*22.98,IIf([ReimbursementCode]="3",[TotalCreditHrs]*61.65,IIf([ReimbursementCode]="4",[TotalCreditHrs]*97.19,IIf([ReimbursementCode]="5",[TotalCreditHrs]*16.01,IIf([ReimbursementCode]="6",[TotalCreditHrs]*51.42)))))).

This expression works great in my report but I cannot get a grand total in the page footer.

Any suggestions??

 

[nobull613]

A quick try might be to enter a new text box in the Report Footer and use the following formula:

=Sum(IIf([ReimbursementCode]="1",[TotalCreditHrs]*18.61,IIf([ReimbursementCode]="2",[TotalCreditHrs]*22.98,IIf([ReimbursementCode]="3",[TotalCreditHrs]*61.65,IIf([ReimbursementCode]="4",[TotalCreditHrs]*97.19,IIf([ReimbursementCode]="5",[TotalCreditHrs]*16.01,IIf([ReimbursementCode]="6",[TotalCreditHrs]*51.42)))))))

 

[dhookom]

IMHO, nesting IIf()s is not a good idea. I expect your rates will change over time and your nasty expression will need to be updated. Your data belongs in tables, not expressions.

I would create a small lookup table of ReimbursementCode vs ReimbRate.
[tt][blue]
tblReimbursementRates
========================
ReimbursementCode ReimbursementRate
1 18.61
2 22.98
3 61.65
....
[/blue][/tt]
You could then add this small table to your report's record source to calculate your values.

Duane
Hook'D on Access
MS Access MVP

 

[joyced]

Thanks, Duane for your response . . . I have created a small look up table of the Reimbursement Codes versus the Reimbursement Rate and that works great!! Thanks for the suggestion - however, for the field "Income From Credit Hours" - I still cannot get a total in the report's footer.

In the report's footer I entered =Sum([ReimbursementRate]*[TotalCreditHrs]) and it just shows up as a "0". All of the other functions I have entered in the report footer has worked excellent but this particular one - I cannot get to give me the sum. Any suggestions?

Again, thanks for the previous post!

 

[dhookom]

If [ReimbursementRate] and [TotalCreditHrs] are numeric fields in the report's record source and this is located in the report footer ([red]not page footer[/red])
=Sum([ReimbursementRate]*[TotalCreditHrs])
it should work.

Duane
Hook'D on Access
MS Access MVP

 

[joyced]

Duane - thanks once again for your assistance. My "Revenue Table" is now complete and works great.

Once again, thanks for your assistance - it is really appreciated.