Check for data in several different fields with one query

[pianoprodigy]

I'm trying to search for data in several different fields. Currently, I have like 12 queries setup to do this. Is there a way to have 1 query check all 12 fields? The data I'm searching for is the same.

I need the record to show up if ANY of the fields contain the information.

 

[dcx693]

Here's one way: create a blank new query and add your table with the 12 fields to it. Add all 12 fields to the query grid (and some other fields if you like). In the criteria line for the first of the 12 fields, enter something like [Please enter the value you want to search for], including the [ and ] brackets. Copy that criteria to the other 11 fields. When you run the query, you'll get prompted to enter your desired value. It will search all 12 fields for that value and return the records that have that value in at least 1 of the 12 fields.

 

[pianoprodigy]

I tried that and it didn't work.

Maybe I'm doing it wrong, but a made a new query.
Added all the fields (biid, umid, bi2id, um2id, resid, pipid)
Typed [Type Criteria] in the Criteria box for each field.

Ran on an id (i.e. 8657) and I returned no records because I think that would make it find 8657 in every field.

I need a record to show up if it appears in one or more of the fields.

 

[pianoprodigy]

Okay. I did a search and found my answer. I guess I do have to enter the [Enter Criteria] in every field's criteria, but I had to go into SQL view and change the AND to OR. I'm proud of myself. My first time use SQL!

I just went back into the Access Query view and saw that I had to put the criteria on different lines diagonally going down.

 

[dcx693]

Okay. I did a search and found my answer. I guess I do have to enter the [Enter Criteria] in every field's criteria, but I had to go into SQL view and change the AND to OR. I'm proud of myself. My first time use SQL!

I just went back into the Access Query view and saw that I had to put the criteria on different lines diagonally going down.

Crap! Sorry about that. My bad. Good for you for figuring it out!