Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving Previous Value (2003)

    I have a column of dates and interest rates. I want to able to enter a date - perhaps in a text box on a form - and to find the linearly interpolated interest rate for that date. That is, if at 1/1/2006 the int.rate is 1% and at 10/1/2006 it is 10% then the interest rate at 4/1/2006 is 4%. To achieve this, how can I find the date value that is on or before the requested date, as well as the date which is on or after the requested date? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieving Previous Value (2003)

    Say that your table is named tblRates, with fields StartDate and Rate. The text box on your form is named txtDate.

    d1 = DMax("StartDate", "tblRates", "StartDate <= #" & Format(Me.txtDate, "mm/dd/yyyy") & "#")

    will return the last date in tblRates on or before txtDate, and

    r1 = DLookup("Rate", "tblRates", "StartDate = #" & Format(d1, "mm/dd/yyyy") & "#")

    will return the rate for that date. Similarly,

    d2 = DMin("StartDate", "tblRates", "StartDate >= #" & Format(Me.txtDate, "mm/dd/yyyy") & "#")

    will return the first date on or after txtDate, and

    r2 = DLookup("Rate", "tblRates", "StartDate = #" & Format(d2, "mm/dd/yyyy") & "#")

    will return the rate for that date.

    BTW, was the reply to <post:=605,701>post 605,701</post:>helpful?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Previous Value (2003)

    Hi. Yes both posts are proving extremely helpful, although I haven't had time to try them out yet.. (Your posts always prove invaluable.) Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •