VBA Code - Replacing Null Values with 0

[backfromthebush]

Hi all,

Im a struggling to come up with a custom function that replaces Null values in a table with Zero.

My table in its basic form has two fields....
Field A: Client
Field B: Quantity

The data is pulled from elsewhere in the database, but when it extracts it will have the client, and quantities... but some clients will have a Null quantity... e.g. below:

CLIENT QUANTITIES VALUE
Bob 9 9
Bill 16 16
Jane 0
Wendy 3 3
Mark 0
Andrew 11 11

I need the "Value" field to populate 0 if the Quantity = Null, otherwise populate with the Quantity value.

I thought the below code may work, but it keeps returning a #Error in the Value field. Any suggestions??

Function Value(Quantity As String) As Integer
If IsNull(Quantity) = True Then
    Value = 0
Else: Value = Quantity * 1
End If
End Function

 

[Joe4]

UDF isn't necessary. If you want to update the table directly, you can use an Update Query, i.e.

UPDATE MyTable SET MyTable.Value = 0
WHERE MyTable.Value Is Null;

If you just want to return it in a Query calculation, use the NZ function, which you tell it what to return in the event of a null value, i.e.

MyValue: NZ([Value],0)+0

We just add zero to the end to coerce it to return a number (the NZ function returns text by default, but adding a zero coerces it to a number).