Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Sep 18, 2022
Solved

Query with User Input Box

hello Experts,  I have a query with 2 different conditions.  1. I can type in part of a company name in a input message box or 2. I can type in a draw number (tblPmtProposalALL.DDNo) in the next m...
access
  • Harun24HR's avatar
    Harun24HR
    Sep 18, 2022

    Tony2021 Try below SQL with IIF() function in where clause. See the attached DB.

    SELECT Table1.ProjID, Table1.DateStart, Table1.DateEnd, Table1.Description, Table1.Rate
FROM Table1
WHERE (((Table1.Description) Like IIf(IsNull([Enter Criteria 1]),"*","*" & [Enter Criteria 1] & "*")) AND ((Table1.Rate)=[Enter DD No or enter for all]));

     Here when you will leave first inputbox blank then it will return just * by IIF() function. which will return all records of description field. Otherwise will return *Your Input Text*.

Harun24HR's avatar
Harun24HR
Bronze Contributor
Sep 18, 2022

Tony2021 Try below SQL with IIF() function in where clause. See the attached DB.

SELECT Table1.ProjID, Table1.DateStart, Table1.DateEnd, Table1.Description, Table1.Rate
FROM Table1
WHERE (((Table1.Description) Like IIf(IsNull([Enter Criteria 1]),"*","*" & [Enter Criteria 1] & "*")) AND ((Table1.Rate)=[Enter DD No or enter for all]));

 Here when you will leave first inputbox blank then it will return just * by IIF() function. which will return all records of description field. Otherwise will return *Your Input Text*.

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Sep 18, 2022

    Harun24HR 

    HI Hasan, thank you for the response. I had to tweak a bit to give me exactly what I wanted but your code pushed me in the right direction.
    this is what I ended up with (added an OR):

    WHERE (((tblBankAccounts.Beneficiary) Like IIf(IsNull([Enter Criteria 1]),"*","*" & [Enter Criteria 1] & "*")) AND ((tblPmtProposalALL.DDNo)=[Enter DD No or enter for all])) OR (((tblBankAccounts.Beneficiary) Like IIf(IsNull([Enter Criteria 1]),"*","*" & [Enter Criteria 1] & "*")));

    I dont completely understand it myself. I did trial and error until I got what I wanted. This was more complicated than I thought. thank you.  FYI: I am not testing for nulls since there would not be a case of any nulls. 

     

    thank you both!

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Sep 19, 2022
      Glad to know!. Then you should tick mark my response instead of your reply.

Resources