How do I search every field in a table with a query

[TheOnlyKickBack]

I want a query to search a table for criteria, and return all records that contain the criteria in ANY field. The criteria is entered using a form, with the following expression, but copying it to every field in the query comes up with #NAME?:

[Forms]![frmCustQuery]![txtQueryInput]

I was wondering if this is possible.

 

[Crowley16]

is this table "set in stone"? if it is, then just add all your fields to the where condition of the query...

if the table can change, then you'd have to use code to loop through the tabledef for that table, and create a dynamic query based on the fields within the table...

--------------------
Procrastinate Now!

 

[TheOnlyKickBack]

If you mean the fields are set in stone, yes they are.

If you mean the records are set in stone, no they're not.

Could you please be more specific, as I have constructed this query in design view and lack knowledge of SQL.

Thank you in advance.

 

[BNPMike]

What Crowley is saying is if your table design is stable you can add the criterion to each field in the query. You seem to have tried this but had some difficulty. Can you post the SQL of the query?

 

[PHV]

Just a hint: use OR (not AND)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

PH

The use of AND instead of OR would just mean the results set would always be zero records or you'd get type mis-match errors. What does the #name mean? I'be got the feeling it means Access doesn't understand a field name - most likely [Forms]![frmCustQuery]![txtQueryInput]?

 

[TheOnlyKickBack]

As requested, here is my SQL code:

SELECT tblCustomer.Cust_ID, tblCustomer.Cust_First, tblCustomer.Cust_Last, tblCustomer.Cust_AddLn1, tblCustomer.Cust_Town, tblCustomer.Cust_Postcode, tblCustomer.Cust_TelNo, tblCustomer.Cust_Email, tblCustomer.Delivery_Discount
FROM tblCustomer
WHERE (((tblCustomer.Cust_ID)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_First)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_Last)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_AddLn1)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_Town)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_Postcode)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_TelNo)=[Forms]![frmCustQuery]![txtCustQueryInput])) OR (((tblCustomer.Cust_Email)=[Forms]![frmCustQuery]![txtCustQueryInput]));

btw mike, PH meant OR instead of AND.

This code comes directly from a query I made in design view. As it uses the OR command, I would have thought this would work, however, even though all names are correct, every field contains "#NAME?" (without speech marks).
Thanks for the replies people.

 

[pbrodsky]

Are you retreiving the result to a form? If so, I've seen something like that before; it involved a conflict between the item names in the table and the field names on the form, but I don't remember the details and haven't been able to reproduce the effect easily.

 

[traingamer]

Try changing your where clause to only check the table fields that are text fields. As pbrodsky pointed out, there is probably some conflict between text, numeric and dates and the only ones that will come out correctly without modification are those that match your txtCustQueryInput type. (If that works, proceed by converting the textbox data to match your specific field types.)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[TheOnlyKickBack]

pbrodsky, i'm not actually putting the results in a form.

traingamer, thanks for the help, it now works great!

by only telling the query to search the text fields, it now does what I want it to do.

thanks again, and to everyone else.

Nathan