Data Conversion Type Error in VBA

Question

I am trying to access elements of a table in Access VBA as part of a function. However, I am getting an error message "Data Type Conversion Error". I can't figure out what I am doing wrong here.

The following subroutine populates the elements of the table that I am trying to access, "HolderTable".

Sub SampleReadCurve()

Dim rs As Recordset
Dim rs2 As Recordset
Dim iRow As Long, iField As Long
Dim strSQL As String
Dim CurveID As Long
Dim MarkRunID As Long
Dim MaxOfMarkAsofDate As Date
Dim userdate As String

DoCmd.RunSQL "DELETE * FROM HolderTable"
'Clears out the old array from the holder table.

CurveID = 15

Dim I As Integer
Dim x As Date

userdate = InputBox("Please Enter the Date (mm/dd/yyyy)")

x = userdate

For I = 0 To 150

MaxOfMarkAsofDate = x - I


strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate, MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("HolderTable")

If rs.RecordCount <> 0 Then

    rs.MoveFirst

    rs.MoveLast

    Dim BucketTermAmt As Long
    Dim BucketTermUnit As String
    Dim BucketDate As Date
    Dim MarkAsOfDate As Date
    Dim InterpRate As Double

    BucketTermAmt = 3
    BucketTermUnit = "m"
    BucketDate = DateAdd(BucketTermUnit, BucketTermAmt, MaxOfMarkAsofDate)
    InterpRate = CurveInterpolateRecordset(rs, BucketDate)
    Debug.Print BucketDate, InterpRate
    rs2.AddNew
    rs2("BucketDate") = BucketDate
    rs2("InterpRate") = InterpRate
    rs2.Update

End If

Next I

Dim vol As Long

vol = EWMA(0.94)

Debug.Print vol


End Sub

This is the function, EWMA, which is giving me the error message. Basically it is just setting up a series of simple operations on the elements of HolderTable, which is populated by values derived in the SampleReadCurve subroutine.

Function EWMA(Lambda As Double) As Double

    Dim Price1 As Double, Price2 As Double
    Dim vInterpRate() As Variant
    Dim SumWtdRtn As Double
    Dim I As Long
    Dim m As Double
    Dim rec As Recordset
    Dim BucketTermAmt As Long

    BucketTermAmt = 3

    Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double

m = BucketTermAmt

Set rec = CurrentDb.OpenRecordset("SELECT InterpRate FROM HolderTable")

Do While rec.EOF = False

rec("InterpRate") = vInterpRate

    Price1 = Exp(vInterpRate(I - 1, 1) * (m / 12))

    Price2 = Exp(vInterpRate(I, 1) * (m / 12))

    LogRtn = Log(Price1 / Price2)

    RtnSQ = LogRtn ^ 2

    WT = (1 - Lambda) * Lambda ^ (I - 2)

    WtdRtn = WT * RtnSQ

    SumWtdRtn = SumWtdRtn + WtdRtn

Loop

EWMA = SumWtdRtn ^ (1 / 2)

End Function

HolderTable has two fields, BucketDate and InterpRate, both of which have the Data Type "Short Text". I get the Data Type Conversion Error message at the line

   Price1 = Exp(vInterpRate(I - 1, 1) * (m / 12))

Changing the Data Type still results in the same error message when run. What am I doing wrong?

Answer 1

Original: Are you sure it's not supposed to be:

vInterpRate = rec("InterpRate")

instead of

rec("InterpRate") = vInterpRate()

Revised:

dim x as integer
Set rec = CurrentDb.OpenRecordset("SELECT InterpRate FROM HolderTable")
x = 0
Do While rec.EOF = False
redim(vInterpRate, x+1) 'need to preserve don't remember if this is default in vba
  vInterpRate(x) = rec("InterpRate")
  x = x + 1
  rec.next
Loop

for i = 1 to x do

  Price1 = Exp(vInterpRate(I - 1, 1) * (m / 12))

  Price2 = Exp(vInterpRate(I, 1) * (m / 12))

  LogRtn = Log(Price1 / Price2)

  RtnSQ = LogRtn ^ 2

  WT = (1 - Lambda) * Lambda ^ (I - 2)

  WtdRtn = WT * RtnSQ

  SumWtdRtn = SumWtdRtn + WtdRtn
next i

There are probably bugs in the code as I don't have a vba compiler and it's been a long time since I've worked in it, but I think this should point you in the right direction if I'm interpreting what you're looking for correctly.