Multiple IIF statements

[KevinMCB]

Just a note, I did search for any recent threads on this subject and none of them related to my problem in a way I could use.

I am trying to calculate monthly interest in a querry for a report on pieces of collateral on a line of credit. I have no problem doing this if the unit has been on the loan for more than a month, in which case I just use

Monthlyinterest: [CurPrin]*[Rate]/12

However, for the units that are added to the loan, say on the 15th of the month, I need to caclulate the interest by day. So far I have come up with the following equation:

Monthlyinterest: IIf(Year(Date())) = Year(Efdate), IIf(Month(Date())) = Month(Efdate), ([OrigPrin]*[Rate])/12, ((Date()-Efdate)*[Rate]*[CurPrin])/365

Where Efdate = date the unit was added to the loan.

Right now I am getting a "wrong number of arguments" error.

I am relatively new to access. If there is a way to accomplish this with having so many imbedded "if" statements that would be great. Otherwise I need to figure out more of the logic on this equation. Thanks!

[weekend00]

I don't know your rule to calculate the interest, but IIF syntax is like this:

IIF( condition1, IIF(condition2, bothTrue, condition2false), condition1false)

[KevinMCB]

Thanks for the tip. Being so new to this I am still learning the language that access accepts.

[KevinMCB]

Ok, I had a co-worker help me out this one, our resident access authority, lol. We finally got it working. Note CurPrin from earlier = OrigPrin Anyway here is the equation:

Monthlyinterest: IIf(Month([Enter date of report])=Month([Efdate]),IIf(Year([Enter date of report])=Year([Efdate]),[OrgPrin]*[Rate]/365*(DateValue([Enter date of report])-[Efdate]+1),[OrgPrin]*[Rate]/365*Day([Enter date of report])), [OrgPrin]*[Rate]/365*Day([Enter date of report]))

This allows it to calculate interest on a monthly basis, but take into account how many days the item has been on the loan. So it can calculate interest per day as well, for a monthly total.

My next project is a running total of the interest. Should be fun!

[weekend00]

you can use one IIF to simplify you expression as:
Monthlyinterest: IIf(Month([Enter date of report])=Month([Efdate]) and Year([Enter date of report])=Year([Efdate]),[OrgPrin]*[Rate]/365*(DateValue([Enter date of report])-[Efdate]+1),[OrgPrin]*[Rate]/365*Day([Enter date of report]))