Forum Discussion
Solved
Dlookup - 3 criteria
Hello Experts,
I am trying to use Dlookup with 3 criteria. The attached excel file is clearer but I will try to explain.
What I need to return is the qryFacilityBal.[Balance] where "ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] and [DateAmend] < " & [tblDraws].[FundingDate].
The 3rd criteria is what I am having problems with. I don’t think it’s a simple < criteria since it seems more like a BETWEEN statement. Maybe an expert knows exactly what I am trying to do.
here is the criteria:
- If the [FundingDate] is < [qryFacilityBal].[DateAmend] then use the MIN [qryFacility].[Balance].
- If the [FundingDate] is <= MAX [qryFacilityBal].[DateAmend] then use the MAX [qryFacility].[Balance].
- If its BETWEEN I am not sure how that would be coded.
Here is my dlookup clause (red part is the issue):
FacAmtDlook: Format(DLookUp("Balance","qryFacilityBal","ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] & " And nz([DateAmend],0) < " & Format$(Nz([tblDraws].[FundingDate],0),"\#mm\/dd\/yyyy\#")),"Standard")
Please see attached xl file. The example is clearer but I have a screen shot below.
grateful for the help. I hope its clear. The excel file I think is easier to follow. Let me know if not clear.
I don't really follow the logic behind that, but I think this should do it:
Public Function GetBalance(dtmFundingDate as Date, lngProjID As Long, lngFacID As Long) As Currency Dim varDateAmend as Variant Dim strCriteria as String strCriteria = "DateAmend <= #" & Format(dtmFundingDate,"yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID varDateAmend = DMax("DateAmend","qryFacilityBal",strCriteria) If Not IsNull(varDateAmend) Then strCriteria = "DateAmend = #" & Format(varDateAmend,"yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk = " & lngFacID GetBalance = DLookup("Balance","qryFacilityBal",strCriteria) Else strCriteria = "ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID varDateAmend = DMin("DateAmend","qryFacilityBal",strCriteria) strCriteria = "DateAmend = #" & Format(varDateAmend,"yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk = " & lngFacID GetBalance = DLookup("Balance","qryFacilityBal",strCriteria) End If End Function
16 Replies
the link above seems to work. I know the ms tech webite was recently reworked. I dont know if there is a different way to upload a file other than the paper clip link in above pic. I dont see any other way.
I guess uploading is not working.
here is a link to the file in my one drive (I hope that shows the full path because it doesnt on my side):
I dont see why its not attaching. I will try again.
The criteria you have given are not mutually exclusive. Do you want to return the balance at the latest date prior to or equal to the funding date? If so you need to do this in two stages, firstly getting the date, then getting the balance at that date. You could do this with an expression, but I’d break it down in a little function like this:
Public Function GetBalance(dtmFundingDate as Date) As Currency Dim varDateAmend as Variant Dim strCriteria as String strCriteria = "DateAmend <= #" & Format(dtmFundingDate,"yyyy-mm-dd") & "#" varDateAmend = DMax("DateAmend","qryFacilityBal",strCriteria) If Not IsNull(varDateAmend) Then strCriteria = "DateAmend = #" & Format(varDateAmend,"yyyy-mm-dd") & "#" GetBalance = DLookup("Balance","qryFacilityBal",strCriteria) Else GetBalance = 0 End If End FunctionPS: I didn't allow for the project ID in the above. You'd need to pass that into the function as a second argument, and add it when assigning values to the strCriteria variable. Ditto for the facility ID.
Hi Ken, in addtion to the projID I also need a where clause on the FacID. that is above my coding IQ though :)
Add them to the function's arguments and then concatenate the values into the criteria for the DMax and DLookup function calls like this:
Public Function GetBalance(dtmFundingDate as Date, lngProjID As Long, lngFacID As Long) As Currency Dim varDateAmend as Variant Dim strCriteria as String strCriteria = "DateAmend <= #" & Format(dtmFundingDate,"yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID varDateAmend = DMax("DateAmend","qryFacilityBal",strCriteria) If Not IsNull(varDateAmend) Then strCriteria = "DateAmend = #" & Format(varDateAmend,"yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk = " & lngFacID GetBalance = DLookup("Balance","qryFacilityBal",strCriteria) Else GetBalance = 0 End If End Function
Hello experts, thank you for the responses.
I have developed a database that I think will facilitate
Please take a look at Q1 (the dlookup field). YOu can see it is not correct.
Ken, if could maybe apply your solution would be excellent. I dont really want to use dlookup but that is what I have used and I know dlookup slows the processing time. I am sure you solution is a better strategy. I can apply it on my production db.
let me know if any questions.
it looks like the accdb did not attach.
I think you should use a generic rule to find the right date/price: "find the highest DateAmended that is smaller than or equal to Funding Date". In that way you will find the price applicable on a specific date.
Having said that, I think you should not find a price for 9/2/2021. There are only prices available from 9/10/2021.
Attach you access database file here.
