Results 1 to 6 of 6
  1. #1
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29

    Date range to determine rate

    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!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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]))))

  3. #3
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    Thanks I will give that a try.

  4. #4
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    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!

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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])

  6. #6
    KevinMCB is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    29
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Rate my schema
    By Hypotenuse in forum Database Design
    Replies: 7
    Last Post: 01-29-2011, 01:20 PM
  2. Date Range Failure
    By goodguy in forum Queries
    Replies: 4
    Last Post: 12-30-2010, 10:22 AM
  3. Using the date to determine if something was late.
    By lyrikkmashairi in forum Access
    Replies: 3
    Last Post: 11-02-2010, 10:59 AM
  4. Date range help!!
    By bananatodd in forum Access
    Replies: 26
    Last Post: 10-18-2010, 01:57 PM
  5. Sum of Values over date range
    By bosnian in forum Queries
    Replies: 1
    Last Post: 01-12-2010, 03:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums