Here's a really cool and easy way to make a power search.
1. Create a form with textboxes for your search fields. They should have a default value "*" for now. The form doesn't need a data source.
2. Create a query on the data source you wish to search. For each field you want searchable change the Criteria to something like this....
[color blue]Like [Forms]![frmPowerSearch]![Criteria3][/color]
When you run this query it will be the results of your search. Best part is, the search criteria can have wildcards such as [color blue]search*[/color] (begins with) or [color blue]*search*[/color] (contains), others too I think.
Of course you can put the query as a subform of your search form and have a button that either requeries it or sets the data source (which requeries). such as...
[color blue]frmPowerSearchResults.Requery[/color]
or
[color blue][Forms]![frmPowerSearch]![frmPowerSearchResults].Form.RecordSource = "qryPowerSearch"[/color]
[color red]: ([/color]
One problem with this method of searching is that null values have bad effects on the ability to search a record. I think a null anywhere in the record causes it to get skipped. In the query you can just use an iif statement in the field: box (where the name of the column is usually displayed) like so....
[color blue]IIf(IsNull([tblCustomers].[Customer])=True," ",[tblCustomers].[Customer])[/color]
Do this for every field that could be null.
Of course there are lots of things you can do to make this search more robust.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.