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
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
Wow. 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)
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