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

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

George_Hepworth

Silver Contributor

Sep 15, 2022

Is dte a field in the table from which qrypricing returns records? Or is it the name of a VBA variable inside the function and, therefore, not available to the query?

arnel_gp

Iron Contributor

Sep 15, 2022

dte is from the 2nd table.
i am querying from the 2nd table.

George_Hepworth
Silver Contributor
Sep 15, 2022
Then it should work work. Perhaps I missed a post, but I don't see the SQL from that query.

Forum Discussion

Dlookup a Rate based on Dates

Resources