CODEWITHSUNDEEP
ms-accessvba
Carl
Carl

Reputation: 5779

Access VBA: sql character to VBA string

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.

Upvotes: 0

Views: 292

Answers (3)

Krish
Krish

Reputation: 5917

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

Upvotes: 1

Comintern
Comintern

Reputation: 22195

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.

Upvotes: 2

Ryan Wildry
Ryan Wildry

Reputation: 5677

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

Upvotes: 1

Related Questions