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

dlook.xlsx12 KB

access

dlookup

Ken_Sheridan

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

Forum Discussion

Dlookup - 3 criteria

Resources