[RESOLVED] write a function to make null field to zero

[lucia]

I am using Vb6.
I want to write a function to replace 'null' to '0'.
I did this:

Public Function sysNulltoZero(ByVal strText As String) As integer

  if strText = Null then
     sysNulltoZero = 0 
  end if

End Function

Then, I call it like this

   SmallCurrencyCharge = Format(sysNulltoZero(rcsCOC("Coc_SmallCurrencyRate")), "000.0000")

But i got an errormsg:invalid use null.

Can someone help me out? Thanks a lot.

[Hack]

Try

if strText = vbNullString then
     sysNulltoZero = 0 
  end if

[lucia]

Thank you Hack,
I tried and got the same errormsg.

[Ellis Dee]

Microsoft Access has a built-in function -- Nz() -- to convert Nulls; you would do well to mirror it. It accepts an optional replacement value for Nulls, which is useful when you want to de-nullify strings. (Setting them to 0 is suboptimal.) Here's code that mimics the Access function:

Public Function Nz(pvar As Variant, Optional pvarReplace As Variant = 0) As Variant
    If IsNull(pvar) Then
        Nz = pvarReplace
    Else
        Nz = pvar
    End If
End Function

Try

if strText = vbNullString then
     sysNulltoZero = 0 
  end if

Null is its own data type; it is not a string. The proper way to test for Null is:

If IsNull(MyVariant) Then

...or...

If VarType(MyVariant) = vbNull Then

It is also worth pointing out that Null is a valid VB keyword, and that it is not equal to vbNull.

[Hack]

I'm trying to duplicate your code for testing. What is:

rcsCOC and Coc_SmallCurrencyRate?

[lucia]

I'm trying to duplicate your code for testing. What is:

rcsCOC and Coc_SmallCurrencyRate?

rcsCOC is a recordset and Coc_SmallCurrencyRate is a field name.

Thanks

[Hack]

If you take what is in the field, store it in a variable, and run it through you function, would that work?

[lucia]

I changed the code to this and it worked.

Public Function sysNulltoZero(ByVal strText As variant) As integer

  if IsNull(strText) then
     sysNulltoZero = 0 
  end if

End Function

Thanks again, Hack.

[brucevde]

The Format function has that capability built right in. The format argument can contain "Zones". The first zone is for positive values, 2nd for negative, 3rd for zero and 4th for Null.

SmallCurrencyCharge = Format(sysNulltoZero(rcsCOC("Coc_SmallCurrencyRate")), "000.0000;;;0")

[lucia]

The Format function has that capability built right in. The format argument can contain "Zones". The first zone is for positive values, 2nd for negative, 3rd for zero and 4th for Null.

SmallCurrencyCharge = Format(sysNulltoZero(rcsCOC("Coc_SmallCurrencyRate")), "000.0000;;;0")

I didn't know that. I tried this and it did give me the 0 but not formatted :

SmallCurrencyCharge = Format(rcsCOC("Coc_SmallCurrencyRate"), "000.0000;;;0")

[brucevde]

I tried this and it did give me the 0 but not formatted :

Change the format as desired

Format(rcsCOC("Coc_SmallCurrencyRate"), "000.0000;;;000.0000")

[lucia]

Change the format as desired

Format(rcsCOC("Coc_SmallCurrencyRate"), "000.0000;;;000.0000")

It worked. I have learned a new thing. THanks Bruce.