Between formula

[Tony2021]

Hello Experts,

I am trying to add a between condition to the following. 

Rate2: Format(DLookUp("Rate","qryPricing","ID=" & [ProjID] & "[dte] between [DateStart] and [DateEnd]"),"Percent")

I know there is something wrong with the syntax after the & sign but I cant figure it out. 

In English, I am looking for the qryPricing.[rate] on [projID] where [dte] is between qryPricing.[DateStart] and qryPricing.[DateEnd]

Note: qryPricing is not in the query. The [DateStart] and [DateEnd] are both in qrypricing.

thank you very much.

Let me know if there are any questions. 

[#2]

Which table does [dte] belongs? maybe:

Rate2: Format(DLookUp("Rate","qryPricing","ID=" & [ProjID] & " And " & [dte] & " between [DateStart] and [DateEnd]"),"Percent")

[#3]

@arnel_gp 

HI Arnel,

[dte] is tblLetterOfCredit.UltimateExpirey (it does have an IIF statement if that matters)  

SELECT tblLetterOfCredit.LCID, DateSerial([y],[m],Day([UltimateExpirey])) AS dte
FROM tblLetterOfCredit, zzTblMonths, zzTblYears
WHERE (((DateSerial([y],[m],Day([UltimateExpirey]))) Between IIf([DateOfIssue] Is Null,[ExpectedDateOfIssue],[DateOfIssue]) And [UltimateExpirey]) AND ((tblLetterOfCredit.UltimateExpirey) Is Not Null))

ORDER BY tblLetterOfCredit.LCID, DateSerial([y],[m],Day([UltimateExpirey]));

here is a screen shot of the above SQL:

FYI:  the dlookup did not return anything but NULL. 

Let me know what is next.  

[#4]

HI Arnel, just checking in with you. Let me know if you have another suggestion. Looking forward to your response.