Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Sep 15, 2022
Solved

Dlookup a Rate based on Dates

Hello Experts, I am trying to lookup a rate in a qry (qryPricing) and it returns the rate but its not according to the Date parameters (the <=). I think there is something wrong after the <= sign be...
access
dlookup
  • arnel_gp's avatar
    arnel_gp
    Sep 15, 2022

    Tony2021 

     

    create a function in a Module:

    Public Function fnLastDate(ByVal sDate) As Date
' arnelgp
'
' note:
'
' sDate is in format YYYY-MM
'
Dim dte As Date
' get the first date
dte = CDate(sDate & "-01")
' get the last date
fnLastDate = DateSerial(Year(dte), Month(dte) + 1, 0)
End Function

    now call the function in your query:

     

    Rate: (SELECT TOP 1 Rate FROM qrypricing WHERE [Date End] >= fnLastDate([dte]) ORDER BY [Date End])

Harun24HR's avatar
Harun24HR
Bronze Contributor
Sep 15, 2022

Please share a sample db. Another thing, are using DLOOKUP() in form or in query? You may give a try on following-

Nz(DLookUp("[Rate]","[qryPricing]","[ProjID]=" & [ProjID] & " And [DateStart] <= " & Format$([dte], "\#mm\/dd\/yyyy\#"),0)

Resources