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

Tony2021's avatar
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!
Tony2021's avatar
Tony2021
Iron Contributor
Sep 16, 2022

Tony2021 

Hi Arnel,

I am now using your function.  It returns the correct rate however I am now getting an error in my crosstab on [dte} saying the field "is not recognized as a valid field name or expression" screen shot below. I reverted to the function thinking that it might fix the error on [dte]. 

 

Do you think the crosstab might not like how Rate is being calculated? :

(SELECT TOP 1 Rate FROM qrypricing WHERE [DateEnd] >= fnLastDate([dte]) ORDER BY [DateEnd]) AS Rate

 The query works fine alone.  No errors on [Rate] or [dte]....only errors when I drag it into the crosstab. 

Error: 

in the crosstab: 

 

I ONLY GET THIS ERROR WHEN I USE THE TOP 1 RATE in the Rate calculation.  I know this because I removed the RATE calc in 3_qryForecastActiveNotActive and I didnt have the error in the crosstab so I know its something to do with the Rate calc.

 

Crosstab: 

built off of 3_qryForecastActiveNotActive. 

TRANSFORM Sum([3_qryForecastActiveNotActive].Amt) AS SumOfAmt
SELECT [3_qryForecastActiveNotActive].CompanyName, [3_qryForecastActiveNotActive].LCName, [3_qryForecastActiveNotActive].LCID
FROM 3_qryForecastActiveNotActive
WHERE ((([3_qryForecastActiveNotActive].dte)<"2040-12") AND (([3_qryForecastActiveNotActive].ProjID)=3))
GROUP BY [3_qryForecastActiveNotActive].CompanyName, [3_qryForecastActiveNotActive].LCName, [3_qryForecastActiveNotActive].LCID
PIVOT [3_qryForecastActiveNotActive].dte;

3_qryForecastActiveNotActive:

(works fine alone...only get error when drag into a crosstab)

SELECT [2_qryDaysActiveNotActive].LCID, [2_qryDaysActiveNotActive].dte, Format(Nz(DSum("Amends","1_qryTotalAmends","LetterOfCreditID=" & [2_qryDaysActiveNotActive].[LCID] & " And [DateAmendSentToBank] <= #" & Format$([dte],"mm/dd/yyyy") & "#"),0),"Currency") AS Amt, tblProjectNames.ProjName, tblCompanies.CompanyName, tblLetterOfCredit.LCNo, tblProjects.ProjID, tblLetterOfCredit.LCName, tblLetterOfCredit.ExpiredYN, (SELECT TOP 1 Rate FROM qrypricing WHERE [DateEnd] >= fnLastDate([dte]) ORDER BY [DateEnd]) AS Rate, [amt]*([Rate]*30/360) AS Fee
FROM tblProjectNames INNER JOIN (((tblProjects INNER JOIN tblLetterOfCredit ON tblProjects.ProjID = tblLetterOfCredit.ProjIDfk) INNER JOIN tblCompanies ON tblLetterOfCredit.Beneficiary = tblCompanies.CoID) INNER JOIN 2_qryDaysActiveNotActive ON tblLetterOfCredit.LCID = [2_qryDaysActiveNotActive].LCID) ON tblProjectNames.IDProjName = tblProjects.ProjectName
WHERE (((tblLetterOfCredit.ExpiredYN)<>"Terminated"))
ORDER BY [2_qryDaysActiveNotActive].LCID, [2_qryDaysActiveNotActive].dte;

 

grateful for your response sir. 

Tony

 

Resources