Solved Error Trapping when a Select Query cannot find a Match. (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
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;

Code:
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

Super Moderator
Staff member
Local time
Tomorrow, 10:52
Joined
Jan 20, 2009
Messages
12,849
You have written code to do the same job as can be done in a single DLookup expression
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
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

Enthusiastic Amateur
Local time
Today, 23:52
Joined
Sep 21, 2011
Messages
14,048
Test for EOF or Record count ?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
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

Enthusiastic Amateur
Local time
Today, 23:52
Joined
Sep 21, 2011
Messages
14,048
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

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
Gasman, yep understood. Will give it a go. I've seen code that i should be able to adapt/borrow. Cheers
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:52
Joined
Jan 23, 2006
Messages
15,364
@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

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
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

Ignore List Poster Boy
Local time
Today, 19:52
Joined
Apr 27, 2015
Messages
6,286
At any rate, congratulations on your "pivotal moment". As insignificant as it may seem, it is the first of many more to come!
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 12:52
Joined
Apr 1, 2019
Messages
712
People, all fixed. Used EOF. Thanks
 

isladogs

MVP / VIP
Local time
Today, 23:52
Joined
Jan 14, 2017
Messages
18,186
For future reference, you could also have used DCount to check if a record exists and if that returns 0, exit the function.
 

Users who are viewing this thread

Top Bottom