Add a WHERE condition to TOP 1

[Tony2021]

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

[#2 · Accepted solution]

@Tony2021 

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.

[#3]

@George Hepworth 

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-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