2 Replies - 11432 Views - Last Post: 22 March 2013 - 07:04 AM

#1 depricated  Icon User is offline

  • DLN-000

Reputation: 718
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

VBA: Handling Null values in Recordset

Posted 15 March 2013 - 05:09 AM

Can anyone share a more efficient way to handle null values from a Recordset? The code I'm currently using is below, and some code I've attempted to use to make it better. Of course, the problem I'm running into is that I can't pass a null value to a separate function so I can't separate out the logic check. I'm tring to reduce the amount of code here.

So this is what I'm doing that does work. Yes, it's a single line of code, but it's cumbersome to type and I keep thinking back to not reusing code over and over when I can separate it out into a function.

lstItem.text = IIf(IsNull(rs!strProcStep), "", rs!strProcStep)


So I've tried to break that off into a function like so:

lstItem.text = SafeInsert(rs!lngIntID)

. . .

Private Function SafeInsert(Item As String) As String
    If IsNull(Item) Then
        SafeInsert = ""
    Else
        SafeInsert = Item
    End If
End Function



Is This A Good Question/Topic? 0
  • +

Replies To: VBA: Handling Null values in Recordset

#2 depricated  Icon User is offline

  • DLN-000

Reputation: 718
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Re: VBA: Handling Null values in Recordset

Posted 15 March 2013 - 05:23 AM

As usual, after posting I was struck by inspiration and redid it thusly:

Private Function SafeInsert(lstItem As Variant) As Variant
    If IsNull(lstItem) Then
        SafeInsert = ""
    Else
        SafeInsert = lstItem
    End If
End Function


Variants can be received and passed as Null. This kicks back a 0-length string instead.

If there is a better way to handle this still I'd love to hear it, but this is what I was going for and got it working :)
Was This Post Helpful? 0
  • +
  • -

#3 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VBA: Handling Null values in Recordset

Posted 22 March 2013 - 07:04 AM

lstItem.text =rs!strProcStep & ""


will be enough

This post has been edited by thava: 22 March 2013 - 07:04 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1