Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 09, 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...
access
dlookup
  • Ken_Sheridan's avatar
    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

     

Ken_Sheridan's avatar
Ken_Sheridan
Brass Contributor
Nov 10, 2024

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.