Forum Discussion
Add a WHERE condition to TOP 1
Hello Experts,
Can I add another WHERE condition to the below? The first one works (single WHERE condition) but I need to add the additional WHERE condition for ProjID = ProjID but I get a datatype mismatch in criteria or expression.
Rate: (SELECT TOP 1 Rate FROM qrypricing WHERE [DateEnd] >= fnLastDate([dte]) ORDER BY [DateEnd])
below is my modification:
Rate: (SELECT TOP 1 Rate FROM qrypricing WHERE [DateEnd] >= fnLastDate([dte] & ProjID = ProjID) ORDER BY [DateEnd])
thank you
The ampersand is the concatenation operator.
You need to spell out the word "and" to include a second criteria.
Context is also important. It's not clear where this SQL appears, and if the two criteria are intended to be form control references.
The ampersand is the concatenation operator.
You need to spell out the word "and" to include a second criteria.
Context is also important. It's not clear where this SQL appears, and if the two criteria are intended to be form control references.
Hi George, thanks for the response. I wasnt expecting this question to go very far. I did end up using "AND" i/o "&" in the TOP 1 query (after [dte]: and projid=projid ) in blue below but it still didnt seem to work. I didnt get an error or syntax but it didnt return the correct rate for projID.
The below Dlookup (by Harun in the link to the question below) is a possible workaround of the TOP 1 method (by Arnel, that I dont understand well enough how to modify it).
Does the between statement look ok? I get a msgbox on DateStart but I confirmed its the field name on qryPricing so not sure if there is something wrong with the between statement. Maybe there is a better way to write it.
feetest: Nz(DLookUp("[Rate]","[qryPricing]","[ProjID]=" & [ProjID] And [DateOfIssue] Between [DateStart] And [DateEnd]))
this question is a continuation of:
the below could probably be ignored since I dont understand it well.
Dlookup a Rate based on Dates - Microsoft Community Hub
What I need to do is somehow modify the below for [ProjID]. What I have below works but I have added other projects to the db and I now need that flexibility instead of just 1 project.
here is the query in full:
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, Format((SELECT TOP 1 Rate FROM qrypricing WHERE [DateEnd] >= fnLastDate([dte]) ORDER BY [DateEnd]),".00000") 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;this is the fnLastDate
Public Function fnLastDate(ByVal sDate) As Date
' arnelgp, used in frmPricing
' note:
' sDate is in format YYYY-MMDim dte As Date
' get the first datedte = CDate(sDate & "-01")
' get the last date
fnLastDate = DateSerial(Year(dte), Month(dte) + 1, 0)
End Function
