Using a function to convert Null to ""

[EagleTempest]

VB6 and Access.

I was looking for a work around when adding new records with blank (null) fields.

I thought I'd create a function to check each field such as:

Public Function CheckNull(strCheck As String) As String
  If strCheck = "" Then
    CheckNull = "yes" 'dummy filler code for checking
  End If
End Function

I guess by sending it back as a String it's no longer a Null item but an empty string and therefore I don't get any errors about "Field cannot be a zero length string".

I'm guessing this is bad programming although a cute trick. Is there an easy way to convert any Null textbox value to "" when adding to a record to a database?

 

[BobJacksonNCI]

txtBox=Nz(txtBox)

Nz changes null to zero for numeric fields and zero-length for text. Or, you can force the setting:
txtBox=Nz(txtBox,"")


HTH,
Bob

 

[johnwm]

Bob,
This is a VB6 question - I believe NZ is only available in VBA without a separate (and clumsy) reference

EagleTempest,
If you're using a function that expects a string, the function will fail if you pass it a Null. Also you're not testing for Null in the function, you're testing for empty string.

You can just add an empty string to resolve the original problem:

myVariable = "" & myVariable

This will ensure that myVariable is not Null

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[EagleTempest]

Thanks for your help. Actually I don't get an error when the function is sent a Null value. That's why I'm was thinking this is bad programming by "converting" the null to "" in passing it to the function.

 

[SonOfEmidec1100]

EagleTempest - johnwm is right - you WILL get an error with your function if you pass it a null. You could take johnwms simple solution and wrap in a function that returns the same string unless its a null then it returns an empty string. Note strCheck must be a variant datatype.

Public Function HandleNull(strCheck As Variant) As String
    
    HandleNull = "" & strCheck

End Function

 

[BobRodes]

Johnwm's solution is elegant and simple, and I usually use it too. If you want more control over how you handle nulls, you can also use the IsNull function, e. g.

If IsNull(MyRs!MyField) then
txtMyText.Text = "No Value Specified"
Else
txtMyText.Text = MyRs!MyField
End If

 

[pweegar]

And once you get your table filled with empty strings (NOTICE not NULL values), set the fields in your table to NOT allow 0 length values. This way NULLs won't get into your tables.

Thenin your vb code, I would check for 0 length values in the fields you don't want NULL values.