Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Power Search Simply

Mega Search

Power Search Simply

by  leobaby  Posted    (Edited  )
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.

Enjoy
leobaby
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top