Forum Discussion
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...
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*.
you may also try:
...
...
WHERE (((tblBankAccounts.Beneficiary) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.Notes) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.DDNo)=IIF(ISNULL([Enter DD No or enter for all]),tblPmtProposalALL.DDNo, [Enter DD No or enter for all])));
...
...
WHERE (((tblBankAccounts.Beneficiary) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.Notes) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.DDNo)=IIF(ISNULL([Enter DD No or enter for all]),tblPmtProposalALL.DDNo, [Enter DD No or enter for all])));