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.

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 Functionnow call the function in your query: Rate: (SELECT TOP 1 Rate FROM qrypricing WHERE [Date End] >= fnLastDate([dte]) ORDER BY [Date End])

Dlookup a Rate based on Dates | Microsoft Community Hub

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)
arnel_gp
Steel 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])
- 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
    Steel Contributor
    Sep 15, 2022
    dte is from the 2nd table.
    i am querying from the 2nd table.
- Tony2021
  Steel 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
    Steel 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

Forum Discussion

Dlookup a Rate based on Dates

Resources