Looking for VB6 Equivalent for NZ() 1

[waldemar]

I am trying to convert the Code of an Access 2000 into VB6; however I get an error, the compiler doesn't recognize the function Nz(). Does anybody quickly know an equivalent? (Checks for Null, if True returns a speficied Value)

Thanks
waldemar

 

[sunaj]

Check out the Isnull() function. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[MarkSweetland]

I use a homegrown version:

Public Function NT(val As Variant, defVal As Variant) As Variant
On Error GoTo err_handler
If Trim(val) = "" Or IsNull(val) Then
'return default value
NT = defVal
Else
'return original value
NT = val
End If
err_exit:
Exit Function
err_handler:
MsgBox "The following error has occurred: " & vbNewLine & _
Err.Description, vbCritical, "Error"
Resume err_exit
End Function



Mark

 

[Norris68]

Not wishing to put the dampers on, but this will fail if the val parameter cannot be Trim'ed e.g. if it is Null.

Public Function NZ(ValueToTest As Variant, ValueIfNull As Variant) As Variant
If IsNull(ValueToTest) Then
    NZ = ValueIfNull
Else
    NZ = ValueToTest
End If
End Function

... no need for an error handler as it will either be Null or not.

By the way, waldemar said he was converting from Access ... you cannot use this NZ function within a query like you can in Access, and there is no way round it.

 

[MarkSweetland]

The Trim() function string argument is any valid string expression. If string contains Null, Null is returned.


Mark

 

[waldemar]

Cheers

 

[strongm]

Be very careful here. Trim returns a variant not a string, which does indeed mean that it can return NULL if asked passed NULL as a parameter. Trim$, on the other hand, returns a genuine string, and will error if the parameter passed to it is NULL.

This observation is true for almost all of the string manipulation functions which have variant and true string versions.

In my opinion, the use of variants should be discouraged where possible for a variety of reasons, the main one being that they are a combination of weak-typing and implicit casting, which can lead to unexpected errors.

Actually, maybe this is an example of the sort of topic that CajunCenturion is looking to discuss in thread222-316590...