base query on optional criteria entered in a form

[btrini]

I have a form on which the user can select any one or combination of criteria. Based on the combination entered, I would like to query my table. I am thinking this can be done in code by using if...then..else. However I am having difficulty writing an AND clause. Can someone tell me if this is the best method and if it is, then how do I write an if clause with and AND. e.g if ((month=Jan) and method=cash) and (payment>200)) then run query all or if ..... etc..... Any help appreciated.
Brian

 

[Terpsfan]

Here is an example using a table called tblAccounts and a form called frmAccounts...you use the full form reference to the field you want to run criteria on.


SELECT *
FROM tblAccounts
WHERE AccountNumber = Forms!frmAccounts!AccountNumber AND LastName = Forms!frmAccounts!LastName;

 

[mikeyb540]

I dont think you need an IF..Then
If the user select the following:
Month=Jan
Method=Cash
Payment>200

Add the fields to the query's criteria like this
Forms![FORMNAME]![month]
Forms![FORMNAME]![method]
Forms![FORMNAME]![payment]

So when the user selects the data from the fields just add a command button to query.
Docmd.openQuery "QueryName"

Thats the idea but you have to test it out.

Mike

 

[btrini]

Ok, but what if the user selects another combination of criteria. In other words, I need the user to enter any combination and then have the query run based on which criteria is selected.