Option ByVal avariable as integer, returns zero when no value given 1

[VBRookie]

Hi,

I think that I have a simple one here. I have a parameter defined as an optional integer being passed into a subroutine. If I don't provide a value for the parameter, it comes out as a value of zero in the subroutine. How do I make it so that if no value is given, nothing or null is returned as opposed to zero. I'm using the following code:

Private Sub IReference_getReferences(ByVal refCategory As Integer, Optional refNumber As Integer, Optional ByVal orderBy As String)
    Dim sql As String
    
    sql = "select * " & _
          "from site_reference " & _
          "where ref_category = " & refCategory
    
    If Len(refNumber) > 0 Then
        sql = sql & " and ref_Nbr = " & refNumber
    End If
    
    If Len(orderBy) > 0 Then
        sql = sql & " order by " & orderBy & ""
    End If
    Set rs = adoConnection.Execute(sql)
End Sub

A point in the right direction here would be greatly appreciated. Thanks.

- VB Rookie

 

[scorpio66]

Optional parameters should be Variant, and you want to use IsMissing to check for their existence. If they are not variant they are initialized to their default values (0 in the case of numbers, "" for strings, 30Dec 1899 00:00:00 for dates)

Public Sub test(Optional var as Variant)
  If IsMissing(var) Then MsgBox "var not here!"
End Sub

Chaz

 

[VBRookie]

THANKS A MILL CHAZ!!! (2 mill even) :-D

It works fine now.

- VB Rookie

Thought of the day:

I was feeling down because I had no shoes, until I met a man who had no feet. So I asked him, 'Hey, you got any shoes you're not using?'

 

[VBRookie]

THANKS A MILL CHAZ!!! (2 mill even) :-D

It works fine now.

- VB Rookie


Simple Thoughts:
The beginning of happiness is the letter 'H'.