Forum Discussion
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 message box.
The WHERE clause seems like its ignoring the number I type in the 2nd input box (tblPmtProposalALL.DDNo) when I leave the first input box empty (Null).
QUESTION: How can I modify the WHERE clause to return the records when I leave the first input box empty (I just hit enter) and I type a number in the 2nd input box (tblPmtProposalALL.DDNo)? The number I can enter is for example, any number between 1 - 100.
SELECT tblBankAccounts.ID, tblBankAccounts.CoIDfk, tblBankAccounts.Name, tblBankAccounts.Beneficiary, tblPmtProposalALL.Notes, tblPmtProposalALL.DDNo, tblBankAccounts.Comments, tblPmtProposalALL.Amount, tblBankAccounts.Bank, tblBankAccounts.TypePmt, tblBankAccounts.[Account No], tblBankAccounts.Attn, tblBankAccounts.ABA, tblBankAccounts.SWIFT, tblBankAccounts.[Correspondent Bank], tblBankAccounts.[Correspondent SWIFT], tblBankAccounts.IBAN, tblCurrency.Currency, tblPmtProposalALL.Currency, tblBankAccounts.Type, tblBankAccounts.ConfirmedYN, tblPmtProposalALL.Reference
FROM tblPmtProposalALL INNER JOIN (tblBankAccounts LEFT JOIN tblCurrency ON tblBankAccounts.Currency = tblCurrency.ID) ON tblPmtProposalALL.ID2 = tblBankAccounts.CoIDfk
WHERE (((tblBankAccounts.Beneficiary) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.Notes) Like "*" & [Enter Co Name part] & "*")) OR (((tblPmtProposalALL.DDNo)=[Enter DD No or enter for all]));
tblPmtProposalALL.DDNo format is Number, Double.
thank you
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*.
- Share a sample db file file.
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*.
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!
- Glad to know!. Then you should tick mark my response instead of your reply.
- 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])));
