Problems with passing decimals to vba

Question

I am having some trouble passing a decimal number from an access field (using a DAO recordset) to a vba variable. I have tried changing datatypes in access and vba (single / currency), but the same line keeps being highlighted in the debugger, with a Runtime error 94 "Invalid use of null". The thing that is confusing me is, when I hold the cursor over the variable in the debugger; the quickinfo shows that the variable is holding the correct value, but the recordset field is null. This only happens for the Decimal field, Other fields (string / integer) are not highlighted.

The line that is higlighted in the debugger is rs!Field4 in the 'Start Loop block. The strange thing is that rs.Field3 sets the numField3 variable fine. The only difference between the two is that field 3 contains integers and field 4 contains decimals. Both were msAccess numbers, but i have changed to currency during problem solve. Any feedback would be good Thanks

    Private Sub Command1_Click()

    'Set Database and Recordset variables
    Dim thisDB As DAO.Database
    Dim rs As DAO.Recordset
    Set thisDB = CurrentDb
    Set rs = thisDB.OpenRecordset("tblTable1")

    'Declare and set recordset variables variables
    Dim strField1 As String
    Dim strField2 As String
    Dim numField3 As Currency
    Dim numField4 As Currency
    Dim dtField5 As Date
    Dim dtField6 As Date

    'Declare dynamic variables
    Dim dtVar1 As Date 
    Dim intVar2 As Integer 
    Dim dtVar3 As Date 

    'DecalreSQL Variables
    Dim num1 As Currency
    Dim num2 As Currency
    Dim num3 As Currency

    Dim StrSQL As String
    Dim strValues As String

    'Start Loop
    Do While Not rs.EOF
        strField1 = rs!Field1
        strField2 = rs!Field2
        numField3 = rs!Field3
        numField4 = rs!Field4
        dtField5 = rs!Field5
        dtField6 = rs!Field6

    If strField1 = "This" And strField2 = "That" Then
    'Perform calculations
        dtVar1 = 0
        dtVar3 = 0
         num3 = 0

    Do While dtVar3 < dtField6

        If ’Something’ Then
        This calculation sets variables            
        ElseIf ‘SomethingElse’ Then
        This calculation sets variables            
        Else
        This calculation sets variables
        End If


        'Build SQL Query and apprend to table
        strValues = dtVar1& "," & num1 & "," &num2 & "," & num3
        StrSQL = "INSERT INTO tblTable2 (Field1, Field2, Field3, Field4 );"
        StrSQL = StrSQL & "VALUES ('" & strValues & "')"

        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSQL
        DoCmd.SetWarnings True

        dtVar3 = dtVar1
    Loop

    End If
    rs.MoveNext
    Loop
    rs.Close
    End Sub

If you show us some code it might help stackoverflow.com/help/mcve — Rémi
Remi, I added some code above Ryan Wildry, I tried the variant data type and the code did nothing. — damnthing
Don't use DoCmd.SetWarnings False and DoCmd.RunSQL StrSQL. Instead, try using thisDB.Execute StrSQL, dbFailOnError. — Gord Thompson
have you tried explicit conversion before passing on the value? — Erick 3E

dbmitch dbmitch · Accepted Answer · 2016-09-02T03:25:32

When you hold the cursor over the variable it hasn't been assigned yet. You're actually showing the last record's value that was assigned to the numField4 variable.

What that means is your current recordset field Field4 is actually a null. To assign it to the numeric variable you need to trap for that condition.

numField4 = Nz(rs!Field4, 0)

And you should probably repeat for Field3 to cover thst possibility too

Problems with passing decimals to vba

1 Answers