Set errors to zero in function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
Back
Top