Date range to determine rate

[KevinMCB]

Hello all, once again I am back for some help. This one is a bit different. This IIf statement is going into a very large query so I will just explain the parts that pertain to it.



Two tables are involved. One with two fields names [OrigPrin] and the other [Curtdue]. I am trying to calculate curtailments based off of teh Original Principle and the term and rate in another table.

The Second table is the customer table. Each entry is a different customer and to calculate the curtailments there are several fields. Term 1 , Rate 1, Term 2, Rate 2, Term 3, Rate 3, etc for the conditions on the curtailments. The term fields are just a numerical number, for instance if curtailments for that certain customer start the 13th month after the [Efdate] (when the loan was added) then the entry would be 13.

Anyway I haver trouble with syntax when building expressions and equations for Access as I am not that familiar with all the language. The following is what I have written so far:


IIf((Month(Now())-[Efdate])>=[Term 1] and <[Term 2], [OrigPrin]*[Rate 1],IIf((Month(Now())-[Efdate])>=[Term 2] and <[Term 3], [OrigPrin]*[Rate 2],Iff((Month(Now())-[Efdate])>=[Term 3] and <[Term 4], [OrigPrin]*[Rate 3]

Something to this affect is what I want to happen. If the current date - the Efdate is greater than or equal to Term 1 and less than Term 2 then the original principle is multiplied by Rate 1. So on and so forth.

Below is the SQL of the entire query without this new equation if you would like to see that as well:

SELECT [COLLATERAL MASTER].DLR, [COLLATERAL MASTER].VIN, [COLLATERAL MASTER].YR, [COLLATERAL MASTER].MAKE, [COLLATERAL MASTER].MODEL, [COLLATERAL MASTER].Efdate, [COLLATERAL MASTER].OrgPrin, [COLLATERAL MASTER].Rate, IIf(Month([Enter date of report])=Month([Efdate]),IIf(Year([Enter date of report])=Year([Efdate]),1,0)) AS AddedThisMonth, IIf(IsNull([pdate]),0,1) AS PaidThisMonth, IIf([AddedThisMonth]+[PaidThisMonth]=2,Day([pdate])-Day([Efdate]),IIf([AddedThisMonth]=1,Day([Enter date of report])-Day([efdate])+1,IIf([PaidThisMonth]=1,Day([pdate])-1,Day([Enter date of report])))) AS DaysInt, [OrgPrin]*[Rate]/365*[DaysInt] AS Monthlyinterest, [COLLATERAL MASTER].ID, [Accrued Interest Calc].SumOfMonthlyinterest, [COLLATERAL MASTER].Pdate, [COLLATERAL MASTER].Status, [COLLATERAL MASTER].CurtDue, [COLLATERAL MASTER].CurtDue
FROM DEALERS, [Accrued Interest Calc] RIGHT JOIN [COLLATERAL MASTER] ON [Accrued Interest Calc].ID = [COLLATERAL MASTER].ID
WHERE ((([COLLATERAL MASTER].DLR)=[Forms]![Homepage]![Combo22]) AND (([COLLATERAL MASTER].Status) Like "Active")) OR ((([COLLATERAL MASTER].DLR)=[Forms]![Homepage]![Combo22]) AND (([COLLATERAL MASTER].Status)="Paid") AND ((Year([Pdate]))=Year([Enter date of report])) AND ((Month([Pdate]))=Month([Enter date of report])));



Thanks for taking a look, any help is appreciated!

[weekend00]

some points need to make clear:
1 now()-Efdate return a number of days, you should not use month() on it; if you want to get the number of month between the two dates, use dateDiff("m",now(),Efdate)

2 you need to decide what should be the rate if now()-efdate is < term1 (rate 0) or >term4 (rate 4)

3 I assume term1 < term2 < term3 < term4

IIf(dateDiff("m",now(),Efdate)<[Term 1], OrigPrin*[rate 0], IIf(dateDiff("m",now(),Efdate)<[Term 2], OrigPrin*[rate 1], IIf(dateDiff("m",now(),Efdate)<[Term 3], OrigPrin*[rate 2], IIf(dateDiff("m",now(),Efdate)<[Term 4], OrigPrin*[rate 3], , [OrigPrin]*[Rate 4]))))

[KevinMCB]

Thanks I will give that a try.

[KevinMCB]

I had to make some modifications. I had to move the Efdate before the date determiner otherwise I was getting a negative number for the difference in the dates. I also changed the date determiner to whatever date I enter so I can look at from an earlier date if i choose. I Ended up with this:

Current Curtailment: IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 1],[OrgPrin]*0,IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 2],[OrgPrin]*[rate 1],IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 3],[OrgPrin]*[rate 2],IIf(DateDiff("m",[Enter date of report],[Efdate])<[Term 4],[OrgPrin]*[rate 3],IIf(DateDiff("“m”",[Efdate],[Enter date of report])>[Term 4],[OrgPrin]*[Rate 4])))))

It is working for the first term and rate, but if I have a date in the Term 2 Rate 2 range, it is sending back an error that the expression is too complex to calculate. So I am not sure exactly what is going on, but I will take a look at it on tuesday. Thanks!

[weekend00]

I saw special characters in you expression(in red color):
Current Curtailment: IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 1],[OrgPrin]*0,IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 2],[OrgPrin]*[rate 1],IIf(DateDiff("m",[Efdate],[Enter date of report])<[Term 3],[OrgPrin]*[rate 2],IIf(DateDiff("m",[Enter date of report],[Efdate])<[Term 4],[OrgPrin]*[rate 3],IIf(DateDiff("“m”",[Efdate],[Enter date of report])>[Term 4],[OrgPrin]*[Rate 4])))))



For your case, I would like to recommend function SWITCH(), it can work the same way as multi IIFs, but it's much easier to use:
Current Curtailment: switch(DateDiff("m",[Efdate],[Enter date of report])<[Term 1],[OrgPrin]*0,DateDiff("m",[Efdate],[Enter date of report])<[Term 2],[OrgPrin]*[rate 1],DateDiff("m",[Efdate],[Enter date of report])<[Term 3],[OrgPrin]*[rate 2],DateDiff("m",[Enter date of report],[Efdate])<[Term 4],[OrgPrin]*[rate 3],DateDiff("m",[Efdate],[Enter date of report])>[Term 4],[OrgPrin]*[Rate 4])

[KevinMCB]

Thanks for the tip, I have never seen the Switch function. Everything is working correctly now. I just needed values in all of the term and rate fields and then it calculated correctly.

The Switch function equation you wrote works perfectly as well, and it is a simpler equation so I will use it instead.

Working in Access has been interesting, I usually do not have much trouble with the logic and what I want to do, however my very limited knowledge of the languague and what functions are available is what holds me back. Thanks for the help!