Access 2002: Outputting a zero when passed Null 2

[TheElephantMan]

Hi, all.

I have a function that I'm using to take null fields (such as the result of a left join) and convert them into zero. Here's my code:

'this function is designed to return an integer zero (0)
'if passed a null string: ("") or (null). It will return the
'original value if not null

Function returnZeroOnNull(stringIn As String) As String

'create variable to hold the return value
Dim returnValue As String

'assign default value
returnValue = stringIn

'if the input was/is null, then
If returnValue = Null Or returnValue = "" Then
    'prepare to return a zero
    returnValue = "0"
End If

'return value
returnZeroOnNull = returnValue

The problem is that when I run it on a column that contains null data, I get "#ERROR" in the null fields, not a zero. A little insight into what I'm doing wrong would be GREATLY appreciated.

Thanks,
TEM

 

[RoyVidar]

Try using the isnull function in stead:

[tt]If isnull(returnValue) Or returnValue = "" Then[/tt]

- but one of your problems here, is that this function doesn't take Nulls, because you pass a string (only variants can be Null), so you'll also need to alter the parameter to variant:

[tt]Function returnZeroOnNull(stringIn As Variant) As String[/tt]

Roy-Vidar

 

[PHV]

And what about the Nz function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Ouch - also declare the returnvalue variable as variant, or to make it a bit more convenient, just use the NZ function on the field in stead of using this this function...

Roy-Vidar

 

[psemianonymous]

The built-in Nz() function does exactly what you are trying to accomplish, so stop fixing your function and start using the Nz() function.

Also note that the 'Null' value cannot be stored in a String value, so as RoyVidar mentioned, you have to use a Variant.


Also, on textboxes and such, you can set the Format property to display a zero value when the field is null. I use this property on reports and such all the time. Try a format of "0;0;0;0" on a textbox and see the difference (or just check the helpfiles to see what I mean).