Find records by keyword etc

[Real Wally]

Dear all,

I'd like to build a search function in my DB that is based on a query on fields from multiple tables. The fields are text in and dates format.

What I had in mind was to have a form with a single box to type the search term in, hit enter and magically a form with the matching records pops up.

Anybody know how to do this?

thanks,
Walter

 

[DJN]

Use the Query By Form technique. There is an example on the Microsoft Knowledge base.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q95931

David

 

[Real Wally]

Thanks David.

I'll keep this one in mind. This method allows you to search multiple fields from as many search fields and is an alternative for what I'm looking for.

What I was hoping to get is a method to search multiple fields from just one input field. The one input field for a search terms will search e.g. the keyword field, address field , name field, title, author etc in multiple tables.

Thanks again,
Walter

 

[Mile-O]

You could use the OR statement in your SQL and just keep referencing the same search criteria object on your form i.e

SELECT *
FROM tblBooks
WHERE (([Author] = [Forms]![frmLibrary]![txtSearch]) OR ([Title] = [Forms]![frmLibrary]![txtSearch]));

 

[Real Wally]

Thanks Mile-O-Phile,

I get a syntax error, it says an operator is missing.
I'll play around with your suggestion a bit more though. If I don't get it to work I'll get back to you for maybe some more suggestions?

Walter

 

[Mile-O]

I don't understand how an operator is missing: all I did was make a faux table with a couple of books in it, and then built a query in the Design View.

I've attached a screenshot of the query I built and the SQL View

 

[Real Wally]

I still cannot get it to work properly.

I've made a query (named qSearch), all tables are joined by one-to-many relations, selected the fields I'd like to search, filled in the criteria as you've indicated and made a form from that (frmSearch).
When I start that form a little box pops up in which I should enter my parameter value. (Above that field it reads the criteria, how do I change that to e.g. 'Type your keyword here'?) Anyway, when I enter a searchterm it doesn't find what it is supposed to find (i.e. not what I intended it to find) so I suppose there is another mistake somewhere. On some keywords I get nothing, on others 6 times the same document. In other words, I've got it wrong somewhere, probably in the query?

Am I doing the right thing by making a form from the query? Or should I use the query directly?

Feedback is appreciated,

Thanks,

Walter