IsNull using VB 6 and SQL SERVER 2000

[aspsqlvb]

Hey guys.....

can someone tell me why when I pass a NULL value to this function, the logic comes back as NULL and not an empty string?......
Thanks, Ken

Public Function Field2Str(strValue As String) As String

  If IsNull(Trim(strValue)) Then
      Field2Str = " "
    Else
     Field2Str = Trim(strValue)
  End If
     
End Function

[Comatose]

This was a bit of a toughy for me...

The If Statement will never be true (it will always run the else) because the trim function returns a string. Period. (Actually, that's only partly true, it returns a Variant of subtype String, but for all intents and purposes, it's a string). Since it returns a string, isnull will never return true, because even if there is nothing in the passed value, trim will then return an empty string (not a null string) and therefore, won't be null.... so, I figured I'd remove the trim statement. Still wouldn't return true for me.... then I realized that you declare the variable in the header of the function as a string.... which, automagically (when created) makes it an empty string. Even if you pass it a null value.

The final solution I found (after trying a myriad of other things) was to change the type declaration to a variant in the function header, and to pass it "null" (not vbnullstring (which is a string), and not vbnull (which is a long)). This successfully returned a true from that if block. Here is how I modified the code:

Public Function Field2Str(strValue As Variant) As String
  If IsNull(strValue) Then
      Field2Str = " "
    Else
     Field2Str = Trim(strValue)
  End If
End Function

and I called it like this retval = Field2Str(Null) . I'd also like to point out that your function doesn't return an empty sting if the value is null, it actually returns space. In order to return an empty string you'd have to return "". Let me know if this has helped some...

[aspsqlvb]

Excellent!!!! That was perfect. Easy to understand. Thank You so much!!!!