Forum Discussion
Dlookup - 3 criteria
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
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 Function
PS: 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 FunctionWow. that worked!
I used it like this:
GetBal: GetBalance([tblDraws].[FundingDate],[tblDraws].[ProjID],[tblFacility].[ID])
here is the final output with the Dlookup solution (that was wrong).
thank you very much. Really amazing solution!
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.
