Solved Error Trapping when a Select Query cannot find a Match.

[HillTJ]

Friends,

I'm pretty stoked that I was able to write code to return a value based upon a "select top 1' query. To me it's one of those 'pivotal' moments in learning. There are cases where the select query does not return a value. I trap these cases with an 'on error' routine, but question whether there is a better method. To me, I'd rather not generate the error in the first place rather than trap it after it's occurred. I would really like an opinion on this method & if someone has a better technique, then I'd really like to adopt it. Seems to me that trapping the error after it's occurred is a bit like putting the cart before the horse & something I usually figure out in my code & leave error trapping to those 'unforeseen' errors. Appreciate any input. The code is reproduced below;

Option Compare Database
Option Explicit
Public Function CostAtDate(PartID As Long, SupplierID As Long, TransactionDate As Date)
Dim strSql As String
Dim rst As DAO.Recordset
Dim CostPrice As Currency
Const conJetDate = "\#mm\/dd\/yyyy\#"
On Error GoTo ErrorHandler


strSql = "Select TOP 1 UnitPrice from qryPriceHistorySortedDescByEffectiveDate WHERE EffectiveDate<= " & Format(TransactionDate, conJetDate) & " And SupplierID = " & SupplierID & " and PartID =" & PartID & ""
 
Set rst = CurrentDb.OpenRecordset(strSql)
    CostPrice = rst.Fields("UnitPrice").value
    CostAtDate = CostPrice
    
ExitError:
    rst.Close
    Set rst = Nothing
Exit Function


ErrorHandler:


Select Case Err.Number
    Case 3021 ' Could not find a match in the Recordset
            MsgBox "No Pricing Set Up", vbOKOnly, "Costing Error"
            CostAtDate = 0
        GoTo ExitError
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Cost by Date by Product Error")
        Resume ExitError
    End Select


End Function

 

[Galaxiom]

You have written code to do the same job as can be done in a single DLookup expression

 

[HillTJ]

Galaxiom, yeh i know. May convert it to a dlookup, at least that way i can use 'NZ' to return a zero if no match occurs.A dlookup would be pretty similar syntax to the sql code! Is one approach better than the other?

 

[Gasman]

Test for EOF or Record count ?

 

[HillTJ]

Gasman, so check if record count = 0 ? Meaning did not find a match? Not sure of the code, but seems a good idea. Thanks

 

[Gasman]

Gasman, so check if record count = 0 ? Meaning did not find a match? Not sure of the code, but seems a good idea. Thanks

If it does not find a record, EOF should also be true?
Your choice.

 

[HillTJ]

Gasman, yep understood. Will give it a go. I've seen code that i should be able to adapt/borrow. Cheers

 

[jdraw]

@HillTJ
For clarity, can you tell us in plain English what exactly is the purpose of your function?
I see a couple of things that may not be relevant, but my curiosity would like an answer.

-Your function does not have a return value? Not required, but ??
-Are you saying there may not be a transaction for that part from that supplier on that Date?
-Are you also saying(indirectly) there may be multiple transactions for that part from that supplier on that date and you want the Maximum Price?
-Readers do not know what qryPriceHistorySortedDescByEffectiveDate is.

Not trying to be picky - just looking for details.

 

[HillTJ]

Jdraw, thanks for taking the time. I'll try to explain. I have a product that can be supplied by various suppliers at various prices valid for a ominated time period. A classic many:many situation. I wish to be able to determine the cost of that product, supplied by a nominated supplier at any date. The query generates a table that puts the tables together. It generates a table of all products, suppliers, prices and dates. I then use the sql code to extract the product, supplier & price at the 'top' date , which is the nearest corresponding price less than or equal to that date (fundamentally the maximum date in the range). Thus, returning a single price based upon the 'maximum' date selected.

Works, but there are probably better methods. Yes, the function does return a value (the price for that product from a selected supplier at the selected date). The line CostAtDate = CostPrice does that for me. If there is no match (no corresponding record) an error is generated, i trap it and assign CostAtDate = 0. This too is returned.

The function works and i've thoroughly tested it.

I am a keen amateur and welcome any suggestions. If there's a better way then i'm only too happy to learn. I Learn VBA by example and often don't get the full gist of the code.

I don' t think purposely trapping the error is good practice, bit 'cart before the horse' . Hope i made sense.

Cheers

 

[NauticalGent]

At any rate, congratulations on your "pivotal moment". As insignificant as it may seem, it is the first of many more to come!

 

[HillTJ]

People, all fixed. Used EOF. Thanks

 

[isladogs]

For future reference, you could also have used DCount to check if a record exists and if that returns 0, exit the function.