VBA中的数据转换类型错误 [英] Data Conversion Type Error in VBA

问题描述

我正在尝试在Access VBA中访问表的元素，作为功能的一部分.但是，我收到一条错误消息数据类型转换错误".我无法弄清楚我在做什么错.

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.

以下子例程填充了我尝试访问的表"HolderTable"的元素.

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

这是函数EWMA，它向我显示错误消息.基本上，它只是在HolderTable的元素上设置一系列简单的操作，这些操作由SampleReadCurve子例程中派生的值填充.

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具有两个字段BucketDate和InterpRate，这两个字段的数据类型均为短文本".我在行

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?

推荐答案

原文: 您确定不应该这样吗?

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

vInterpRate = rec("InterpRate")

代替

rec("InterpRate") = vInterpRate()

修订:

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

代码中可能存在错误，因为我没有vba编译器，而且已经从事很长时间了，但是我认为如果我在解释什么，这应该为您指明正确的方向您正在寻找正确的东西.

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.

这篇关于VBA中的数据转换类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！