Access VBA: sql character to VBA string

0
votes

I would like to loop through a recordset in Access VBA and extract the values of a variable as a string in VBA. This won't work:

Sub test()
Dim rs As DAO.Recordset
Dim m As String

Set rs = CurrentDb.openrecordset("myTable")

If Not (rs.EOF And rs.bof) Then
rs.movefirst

Do Until rs.EOF
debug.print rs!myField 'works
MsgBox rs!myField 'doesn't work

rs.movenext
Loop

End If

rs.Close

End Sub

It gives me an error with the msgbox about nulls, but it works fine for debug.print. I can't seem to extract the rs!myField into a VBA string variable. The character variable in the table contains null values, but I should be able to still work with it.

3
ms-accessvba
Try something like: myStr = rs.Fields("MyField").value Then use myStr it in your MsgBox. – Ryan Wildry
I get the same error when I assign the value to myStr, but debug.print works with your code FWIW. – Carl

3 Answers

2
votes

If you have nullable fields and want to treat them as text in VBA, you can "coalesce" them by appending vbNullString: 

MsgBox rs!myField & vbNullString

Concatenation changes the value of the expression to String before it wraps it's passed to MsgBox.

1
votes

I think I see the issue. Null isn't the same thing as vbNullString. See here for more information. It's Java, but the idea holds.

You have to handle the Null occurrence with an if statement like this:

Sub test()
    Dim rs    As DAO.Recordset: Set rs = CurrentDb.OpenRecordset("myTable")
    Dim MyStr As String
    If Not (rs.EOF And rs.BOF) Then rs.MoveFirst

    Do Until rs.EOF
        If IsNull(rs!MyField) Then
            MyStr = vbNullString
        Else
            MyStr = rs!MyField
        End If
        MsgBox (MyStr)
        rs.MoveNext
    Loop

    rs.Close
End Sub
1
votes

You can also simply do MsgBox nz(rs!myField,"Value if null")