Multiple Combo/Text Boxes as Criteria for a Query

[brea]

I need to let users search for information by providing them with a form with many combo or text boxes on it. Then, I want them to be able to push a button that will use the user specified info as criteria for a query. The catch is that I don't want the user to have to enter data in EVERY box. That is, if they leave the box blank, the query will not have any criteria for that field and will search everything. I have tried many different ways of setting the criteria, such as
Like [forms]![formname]![controlname]OR [forms]![formname]![controlname]ISNULL but then every time I would run the query, additional criteria would be added to my query! I am totally stumped! I would really appreciate any help. Thanks!

 

[Mitch]

Use the * char as follows

Like [Forms]![FormName].[ControlName] & "*"

HTH

Mitch

 

[Skip H]

While browsing this site, and others, I have seen a lot of questions pertaining to filter by form, and thought this might help .

Go to Microsoft's site and search for this document Q166634 as described below.

Q166634 - ACC97: Filter-by-Form Example for Run-Time Applications Available in the Download Center.

It consists of a form called frmCreateQBF, and 2 modules.
It makes a copy of your form with unbound text boxes and adds
a OK & Cancel button. This was written for Access/97, and I am using Access/2000. When I tried to create the form, I kept receiving the mesage TYPE MISMATCH. To correct the problem
Microsoft said to
_______________________________________________________
Made sure the following references were checked and in the following order
1 Visual Basic for Applications
2 Microsoft Access 9.0 Object Library
3 Microsoft DAO 3.6 Object Library
4 OLE Automation
5 Microsoft Visual Basic for Applications Extensibility 5.3
6 icmfilter 1.0 Type Library
7 Microsoft ActiveX Data Objects 2.1 Library
___________________________________________________________
This corrected the problem in Access/2000 , and have not had any problems since. In the module basBuildSQL you can comment out the line for the "LIKE" comparrison and use the line "=".
It's documented in the program, you shouldn't have any problem finding it.
This is basically a filterbyform ,hence the name query by form
qbf.exe
____________________________________________________

Hope it Helps

Skip H