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

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

Code:

---


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

Code:

---

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

---

But i got an errormsg:invalid use null.

Can someone help me out? Thanks a lot.

Try

Code:

---

if strText = vbNullString then
    sysNulltoZero = 0
  end if

---

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

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

rcsCOC and Coc_SmallCurrencyRate?

Quote:

---

Originally Posted by Hack

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

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

I changed the code to this and it worked.

Code:

---


Public Function sysNulltoZero(ByVal strText As variant) As integer

  if IsNull(strText) then
    sysNulltoZero = 0
  end if

End Function

---

Thanks again, Hack.

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")

Quote:

---

Originally Posted by 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")

---

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

Code:

---


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

---

Quote:

---

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")

Quote:

---

Originally Posted by brucevde

Change the format as desired

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

---

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

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:

Code:

---

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

---

Quote:

---

Originally Posted by Hack

Try

Code:

---

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.