wild search

  • Thread starter Thread starter sheela
  • Start date Start date
S

sheela

I am having problem in designing parameter query, where
users can search records from a table. I am using a
parameter query. What I need is: If the user doesn't type
any thing in the parameter values, the query should show
all the records. Otherwise if the user types some thing,
the results should show according the parameter values he
typed.
The query looks as follows, except my original query has
lot more fields in the search criteria:

SELECT TableName.Field1, TableName.Field2, TableName.Field3
FROM TableName
WHERE (((TableName.Field1) Like ([param1]) & "*")
AND ((TableName.Field2) Like ([param2]) & "*")
AND ((TableName.Field3) Like ([param3] & "*") );

This query always gives zero records; even it is supposed
to show some records. Could someone please help me in
this search?
 
Remember using the word AND all fields must be true to satisfy the search.
Try using OR where only one field must be true.
 
When I use "OR" criteria, it gives all the records from
the table, no matter what the parameter values are. How do
I implement the parameter values in the results?
-----Original Message-----
Remember using the word AND all fields must be true to satisfy the search.
Try using OR where only one field must be true.

I am having problem in designing parameter query, where
users can search records from a table. I am using a
parameter query. What I need is: If the user doesn't type
any thing in the parameter values, the query should show
all the records. Otherwise if the user types some thing,
the results should show according the parameter values he
typed.
The query looks as follows, except my original query has
lot more fields in the search criteria:

SELECT TableName.Field1, TableName.Field2, TableName.Field3
FROM TableName
WHERE (((TableName.Field1) Like ([param1]) & "*")
AND ((TableName.Field2) Like ([param2]) & "*")
AND ((TableName.Field3) Like ([param3] & "*") );

This query always gives zero records; even it is supposed
to show some records. Could someone please help me in
this search?
Click to expand...


.
Click to expand...
 
When I use "OR" criteria, it gives all the records from
the table, no matter what the parameter values are. How do
I implement the parameter values in the results?
Click to expand...

On the Query grid you'll need to repeat other fields' criteria on
every OR line. If you have an OR with some fields and AND with others,
it's really better to go to the SQL window and use parentheses to
control the order of evaluation. E.g.

WHERE ThisField = Forms!frmCrit!txtThis AND (ThatField =
Forms!frmCrit!txtThat OR TheOtherField = Forms!frmCrit!txtThat)
 
Back
Top