ms-access - 访问报告文本框在总计中给出 #Type 错误

Question

我正在拔头发，因为无法弄清楚如何解决这个问题我总结了 12 个文本框，这些文本框构成了 MS -Access 报告上的一个财政年度。

我在 2 个文本框和总计文本框上收到 #Type 错误，如果我将它们从一月到十二月相加，则错误发生在一月和二月文本框和总计中，如果我从十二月到一月求和，则错误发生在十一月和十二月的文本框和总数是这样的：

=[R_OthSpnd_1_Dec]+[R_OthSpnd_1_Nov]+[R_OthSpnd_1_Oct]+[R_OthSpnd_1_Sep]+[R_OthSpnd_1_Aug]+[R_OthSpnd_1_Jul]+[R_OthSpnd_1_Jun]+[R_OthSpnd_1_May]+[R_OthSpnd_1_Apr]+[R_OthSpnd_1_Mar]+[R_OthSpnd_1_Feb]+[R_OthSpnd_1_Jan]

这让我认为这与文本框处理函数的顺序有关，我检查了函数，文本框似乎从有数据的最高月份开始处理到 1 月？

如果我将文本框中的函数包装在 Nz 函数和/或 Totals 中，它似乎没有区别？

知道如何让它们正确地达到 Total 吗？

十二月至一月总计

一月至十二月合计

VBA 函数如下所示：

Function GetValue(ByRef Lookup_Value As String, ByRef Company_Name As 
                String, ByRef Mth_Value As String, ByRef Year_Value As String)

On Error GoTo Err_GetValue

Dim mydb As DAO.Database
Dim rs_1 As DAO.Recordset
Dim rs_2 As DAO.Recordset
Dim rs_3 As DAO.Recordset
Dim Get_SQL_1 As String
Dim Get_SQL_2 As String
Dim SQL_Return_Value As String
Dim fnDataReturn As String
Dim Lookup_Column_Name As String
Dim Lookup_Values As String
Dim X As Long

Set mydb = CurrentDb
Dim strTypeOfData(3)

For i = LBound(strTypeOfData) To UBound(strTypeOfData) - 1

    strTypeOfData(0) = Lookup_Value & "External"
    strTypeOfData(1) = Lookup_Value & "Internal"
    strTypeOfData(2) = Lookup_Value & "Forecast"

    Get_SQL_1 = "SELECT [SQL_Statement] FROM tblMappingsOtherSpend WHERE [Lookup] ='" & strTypeOfData(i) & "';"
    Get_SQL_2 = "SELECT [Lookup_Column] FROM tblMappingsOtherSpend WHERE [Lookup] ='" & strTypeOfData(i) & "';"
    Set rs_1 = mydb.OpenRecordset(Get_SQL_1)
    SQL_Return_Value = rs_1.Fields("SQL_Statement").Value
    SQL_Return_Value = SQL_Return_Value & Year_Value & Mth_Value & "';"

    Set rs_2 = mydb.OpenRecordset(Get_SQL_2)
    Set rs_3 = mydb.OpenRecordset(SQL_Return_Value)

    Lookup_Column_Name = rs_2.Fields("Lookup_Column").Value

    If rs_3.RecordCount = 1 Then
        fnDataReturn = CLng(rs_3.Fields(Lookup_Column_Name).Value)
        If fnDataReturn <> 0 Or fnDataReturn <> "" Then
            GetValue = CLng(fnDataReturn)
            Exit For
        End If
    End If

    GetValue = 0

    Next i

    rs_1.Close
    rs_2.Close
    rs_3.Close

    Set rs_1 = Nothing
    Set rs_2 = Nothing
    Set rs_3 = Nothing
    Set mydb = Nothing

    Exit_GetValue:

    Exit Function

    Err_GetValue:

    GetValue = 0
    Select Case Err.Number
    Case Else

        GoTo Exit_GetValue
    End Select

End Function

标签： ms-accessvba