Query with User Input Box

[Tony2021]

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

[#2]

Share a sample db file file.

[#3 · Accepted solution]

@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*.

[#4]

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])));

[#5]

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

[#6]

Glad to know!. Then you should tick mark my response instead of your reply.