Forum Discussion

Tony2021

Steel Contributor

Nov 10, 2024

Solved

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

Steel Contributor

Nov 10, 2024

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)

Ken_Sheridan

Copper Contributor

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
Steel Contributor
Nov 11, 2024
Hello Ken, that was the fix! The amount is no longer showing $0. thank you!
Arnel, that worked too! thank you very much.