Nz function issues

[EddyLLC]

I noticed the Nz function doesn't work in VB6. I'm trying to populate a field with data pulled from a text file. I want to left justify and space fill and missing characters. The code I am using in VBA is:

Format(Nz(txtField," "), "!@@@@@@@@@@")

In VB I am getting Sub or Function Not Defined error. Is there a substitute for the NZ in VB?

 

[SBerthold]

No need for Nz().

The functionality you need is all included with in the format function. Break the format argument into two sections using a semicolon to seperate each. In the first use your !@@@ and in the second the spaces.

 

[JoeAtWork]

Nz doesn't exist in VB6 (as far as I know it only exists in Access VBA).

However, it's rather trivial to create your own:

Public Function nz(ByRef vNullCheck As Variant, Optional vResult As Variant) As Variant
        
    If IsNull(vNullCheck) Then
        If Not IsMissing(vResult) Then
            nz = vResult
        Else
            nz = 0
        End If
    Else
        nz = vNullCheck
    End If

End Function

 

[SBerthold]

You should be able to use the Format function alone for this:

>Break the format argument into two sections

?Format(txtField, "!@@@@@@@@@@; ")

 

[ousoonerjoe]

NotNull Function

Public Function NotNull(dbArg As Variant, Optional DefaultValue As String, Optional Trimming As Boolean) As String
    If IsNull(dbArg) = True Then
        NotNull = DefaultValue
    Else
        If Trimming = True Then
            NotNull = Trim(dbArg)
        Else
            NotNull = dbArg
        End If
    End If
End Function

'Usage
Field1 = NotNull(Rst.Field("Field1").Value, 0, True)

I write a lot of flat file exports. Leading spaces from the data field are sometimes required. DefaultValue if left blank returns an empty string.


Pad Function:

Private Function Pad(Values As String, PadWith As String, Digits As Byte, Optional RightPad As Boolean = True) As String
    Dim Str As String
    If Len(Values) > Digits Then
        Pad = Left(Values, Digits)
    Else
        Str = Space(Digits - Len(Values))
        Str = Replace(Str, " ", PadWith, 1, , vbTextCompare)
        If RightPad = True Then
            Pad = Values & Str
        Else
            Pad = Str & Values
        End If
    End If
End Function

I use these together for flat file exports:

Field1 = Pad(NotNull(.Fields("Field1").Value), " ", 10, True)

If it is NULL, then it will return a string with 10 spaces. I'm sure there's another way to do this, but these give me the control and ability to interject error trapping and data checking where necessary.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[BobRodes]

If you wish to replace a null value with an empty string (say when populating text boxes with fields from a recordset), you can concatenate an empty string to the field value, thus:

txtMyBox.Text = rs!myField & ""

 

[SBerthold]

True. But, since the OP wants to also format the field value, the Format$ function will handle the Nulls, returning an empty string, and format the value in the same call.

Side note:
Using the Format function to just handle Nulls as in Format$(TheField.Value) is however slower than using

TheField.Value & ""
or
TheField.Value & vbNullstring

(using the vbNullstring is fastest)

 

[BobRodes]

I didn't mean to recommend it as a solution, rather to point it out as a simplification if you didn't need to provide a default value.

<using the vbNullstring is fastest

Didn't know that.

 

[tedsmith]

I though "Ship may sifully graze" was a NZ function?