Dlookup a Rate based on Dates

[Tony2021]

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

The DateStart and DateEnd are mm/dd/yyyy format while the [dte] field is formatted yyyy-mm.  I am not sure if that is the problem. I tried to change the yyyy-mm to mm/dd/yyyy but that didnt fix it. 

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

in every case the rate of 1.375% is being returned even if the dates are outside of the date start and date end for 1.375%. 

qrypricing:

yyyy-mm (I use this format to sort the data correctly). 

maybe the formula is just simply not correct.

Let me know if additional clarification is needed. 

thank you. 

[#2]

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)

[#3 · Accepted solution]

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

[#4]

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?

[#5]

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

[#6]

Then it should work work. Perhaps I missed a post, but I don't see the SQL from that query.

[#7]

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!

[#8]

@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

[#9]

HI Arnel, I have a solution now. I ended up using a make table query that removed the [Rate] calculation using TOP 1. I was then able to make the cross tab query. thank you for the help!

[#10]

good to know.