Access VBA: sql character to VBA string

Question

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.

Try something like: myStr = rs.Fields("MyField").value Then use myStr it in your MsgBox. — Ryan Wildry
– Ryan Wildry, Commented Feb 15, 2017 at 16:55
I get the same error when I assign the value to myStr, but debug.print works with your code FWIW. — Carl
– Carl, Commented Feb 15, 2017 at 16:59

Comintern · Accepted Answer · 2017-02-15 17:30:06Z

2

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.

answered Feb 15, 2017 at 17:30

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:29:39Z

1

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

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 15, 2017 at 17:11

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

Comments

Krish · Accepted Answer · 2017-02-16 10:18:22Z

1

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

answered Feb 16, 2017 at 10:18

Krish

5,9272 gold badges19 silver badges37 bronze badges

Collectives™ on Stack Overflow

Access VBA: sql character to VBA string

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related