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!
HI Ken, looking a little deeper into the GetBal function I might need a tweak.
GetBal returns a 0 when the [FundingDate] is less than the [DateAmend].
In this case, where it says 0, the GetBal function should return 2.344B (the [Balance] associated with the MIN of [DateAmend]...i believe that is how to say it).
do you see where the tweak might need to be? Thanks again for the help!
It seems to be ok when the FundingDate is > DateAmend (just less than is the issue)