Set errors to zero in function

B

Bruce

I have a function using DLOOKUP which looks up numbers. If the value cannot
be found I want the function to equal zero. I tried the following but it does
not work.

Function myFX(mySource As Integer, myDestination As Integer)

myFX = CDbl(DLookup("[FinStdRate]", "tbl_Currency", "[SourceCountryID]=" &
mySource & " And [CountryID] = " & myDestination))

If iserror(myFX) then
myFX = 0
Endif

End Function

However when I leave out the If statement in the VBA and add it to my text
field in my form it does work.

i.e.

=IIf(IsError(myFX),0,myFX)

Why doesnt this work in VBA? Is their another way to do this?

Bruce
 
O

Ofer

Try the NZ function

myFX = cdbl(nz(DLookup("[FinStdRate]", "tbl_Currency", "[SourceCountryID]=" &
mySource & " And [CountryID] = " & myDestination),0))

that will replace a 0 instead of a null if no record is found
 
O

Ofer

He will get an error when he will try to do cdbl on the null that return in
the dlookup.
this is why it better to assign a zero to the variant before he'll perform a
cdbl, and this why you should use the nz function.

cdbl(nz(dlookup....),0)
so you wont need the if statement after that

ph said:
try this :

If IsNull(myFX) Then
myFX = 0
End If

Bruce said:
I have a function using DLOOKUP which looks up numbers. If the value cannot
be found I want the function to equal zero. I tried the following but it does
not work.

Function myFX(mySource As Integer, myDestination As Integer)

myFX = CDbl(DLookup("[FinStdRate]", "tbl_Currency", "[SourceCountryID]=" &
mySource & " And [CountryID] = " & myDestination))

If iserror(myFX) then
myFX = 0
Endif

End Function

However when I leave out the If statement in the VBA and add it to my text
field in my form it does work.

i.e.

=IIf(IsError(myFX),0,myFX)

Why doesnt this work in VBA? Is their another way to do this?

Bruce
Click to expand...
Click to expand...
 
Top