Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
May 28, 2023

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

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

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    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.

    • Tony2021's avatar
      Tony2021
      Steel Contributor

      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

Resources