Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Apr 03, 2022

Between formula

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 ...
access
arnel_gp's avatar
arnel_gp
Steel Contributor
Apr 04, 2022
Which table does [dte] belongs? maybe:

Rate2: Format(DLookUp("Rate","qryPricing","ID=" & [ProjID] & " And " & [dte] & " between [DateStart] and [DateEnd]"),"Percent")
Tony2021's avatar
Tony2021
Steel Contributor
Apr 04, 2022

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.  

Resources