Forum Discussion
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 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.
- Which table does [dte] belongs? maybe:
Rate2: Format(DLookUp("Rate","qryPricing","ID=" & [ProjID] & " And " & [dte] & " between [DateStart] and [DateEnd]"),"Percent")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.
- HI Arnel, just checking in with you. Let me know if you have another suggestion. Looking forward to your response.
