Forum Discussion
Tony2021
Nov 10, 2024Steel Contributor
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 "ProjI...
- Nov 10, 2024
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
Tony2021
Nov 10, 2024Steel Contributor
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.