HillTJ
To train a dog, first know more than the dog..
- Local time
- Today, 08:24
- Joined
- Apr 1, 2019
- Messages
- 731
All,
I have a query 'qry_Max_Price_Part_Supplier' that returns the latest cost of a product that could be supplied by various suppliers(works). The query contains the following fields 'MaxOfDate_of_Price', ' Cost_Price', "ProductID' (number) & 'JimCode' (string).
I wish to return the latest Cost_Price at that particular date (Not equal to the date), for that product from that supplier.
I thought I had it clear in my Head.
As always, appreciate it.
I have a query 'qry_Max_Price_Part_Supplier' that returns the latest cost of a product that could be supplied by various suppliers(works). The query contains the following fields 'MaxOfDate_of_Price', ' Cost_Price', "ProductID' (number) & 'JimCode' (string).
I wish to return the latest Cost_Price at that particular date (Not equal to the date), for that product from that supplier.
I thought I had it clear in my Head.
Code:
Function GetMaxCost(ProductID As Long, Supplier As String, Date_Quoted As Date)
GetMaxCost = DLookup("Cost_Price", "Qry_Max_Price_Part_Supplier", "ProductID=" & ProductID And "JimCode='" & Supplier & "'" & MaxOfDate_of_Price <= Date_Quoted)
End Function
As always, appreciate it.