Trapping Error Question

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I have a query that uses a function to do a lookup in another table. The
problem I am having was that I was receiving a 94 invalid use of null error
if there was no record in the lookup table. I added a error trap to check
for error 94 and this worked but now I get #ERROR in the query results if
there is not a record in the lookup table. How can I structure the function
to do the lookup and if there is nothing int he lookup table just to return
a different value? See function below and thanks in advance!

Public Function CheckRev(Part As String, Cust As String) As String

On Error Resume Next

Dim tempRev As String

tempRev = DLookup("[UserDefined1]", "dbo_partxreference", "[PartNumber] = '"
& Part & "' and [SupplierID] = '" & Cust & "'")

If Err = 94 Then
CheckRev = rev & user1
Else: CheckRev = tempRev
End If

End Function
 
Back
Top