Query several Columns in same Table

[ericfatherree]

I Have created a simple database to keep up with company contacts. and created a query using ("Like "*" & [enter name or any part of] & "*") in the criteria field. My question is how can I search Multi Columns in the same table instead of it just searching one place. Example type in "Joe or Fire Dept" and it pull up the files I need. I also created a form with the Query so that it pulls all results into one page and it is looking good. Just can not get the search to search my whole table, it just searches "name" or "phone" or Company which ever i place the query coding into.



Do not really need any advance coding, just something that I can leave up and have other co-workers search the contacts, most of the co-works have no knowledge of access so do not know how to change around locations from tables to forms and ect.


Any help would be great.

[alansidman]

This will be helpful and easy to set up and give you flexibility for your users.

http://www.datapigtechnologies.com/f...earchform.html

Alan

[ericfatherree]

That works well for searching boxes but you have to enter all search info in different search boxes. That example gives five different fields. I would like one search box that will search all five columns at same time.

[June7]

Don't do search in a query, use a form. Using a query would require input parameter prompt under each column. With a form, user can choose which fields to input criteria for.

Check out these tutorials
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...tomfilter.html

[ericfatherree]

All of these options are allowing to still only search a specific field for each text box. Example you want a phone number search you have to enter it into the phone number box, if you want a address you have to enter it into address box, I want one box that will allow users to search any field within that table with only "one search box"

[June7]

Try this. Put the same input parameter under each column, each on a different Criteria row so that the OR logical operator is used. Now enter 542 as search criteria. All records with 542 in any field (phone number, address, SSN, credit score, etc.) will be retrieved.

Is that what you want?

[ericfatherree]

Try this. Put the same input parameter under each column, each on a different Criteria row so that the OR logical operator is used. Now enter 542 as search criteria. All records with 542 in any field (phone number, address, SSN, credit score, etc.) will be retrieved.

Is that what you want?

Yes, that is what I am needing. can you give me steps to do that. Im not sure if I follow you on "Put the same input parameter under each column, each on a different Criteria row so that the OR logical operator is used"

Can you explain little more please.

Thanks In Advance

[June7]

Criteria can use AND and OR logical operators. If parameters are all on the same Criteria row, AND will apply. Put parameter under each column on a separate Criteria row in the query design grid (will look like stair steps). The grid shows a row called Criteria and below that 'or'. Keep entering or rows below that. Use your original parameter:
("Like "*" & [enter name or any part of] & "*"). Can't make it any simpler.

Access Help has guidelines on using the query designer.

[ericfatherree]

That is what I needed, it worked GREAT. I had been copying the command accross in same line and like you said it was looking for it wrong. Now it is working great. Thank you so much.

Eric