Forum Discussion

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

Sep 15, 2022

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])

arnel_gp

Iron Contributor

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])

Tony2021

Iron Contributor

Sep 15, 2022

Hi Arnel, I ended up using your TOP 1 trick from a previous query:
Rate: (select top 1 rate from qryPricing where [2_qryDaysActiveNotActive].dte between datestart and dateend)

it worked and is returning the correct rate between startdate and enddate! thank you very much once again!