"Create an input device that can change the criteria of a query":

[Guest]

I have created a query that looks at nine columns of the same type of data in my database. Every time I want to search for something else, I have to go and change each criteria for each column. I would like to be able to have a device (a form maybe) where the search criteria could be inputted, the change made to the query, query ran and results returned all at once.

 

[John Vinson]

I have created a query that looks at nine columns of the same type of data in my database. Every time I want to search for something else, I have to go and change each criteria for each column. I would like to be able to have a device (a form maybe) where the search criteria could be inputted, the change made to the query, query ran and results returned all at once.

Two suggestions: If you're applying the same criterion to all nine
fields, you may want to look at the normalization of your database. Do
you have a one-to-many relationship embedded in each row? What are
these nine fields?

If the table structure is correct, you can create a Parameter Query by
putting

[Enter value:]

on the Criteria line under each of the nine fields, on *separate rows*
of the query grid so that it uses OR logic. If you now open the query
(or a Form or Report based on the query) you'll be prompted Enter
value:, and Access will return those records where that value is found
in any one of the nine fields. Or, you can create an unbound Form
named frmCrit, with nine textboxes matched to the fields. Use a
criterion on each field of

=Forms!frmCrit!txtTextboxN OR Forms!frmCrit!txtTextboxN IS NULL

under each field, using the names of the nine different textboxes in
place of TextboxN. Put a command button on frmCrit to open a Form
based on this query (for onscreen display) or a Report (for printing),
or both.

 

[Sidney Linkers]

Hi,

Add all nine fields together, if they are all of text type, in a new column
in your query design. Then put in it's criteria:

LIKE "*" & [Search For:] & "*"


When you run the query you will be ask to fill in a search criteria "Search
For:". Every record will then be shown where the search criteria is found in
one or more of the nine columns. If you leave the serach criteria empty, the
all records will be shown.

You can use this principle on a form with a textbox for the search criteria
and the results shown in a subform (tabelview).
However you need to put the SQL statement of your query in the recordsource
of the subform and change the parameter field [Search For:] with the textbox
reference (for example [Forms]![frmSearch]![txtSearchFor] if those are the
names of your mainform and textbox). You also will need a "Search Now"
button which requeries the subform.


An SQL statement example with 4 fields for the subform (tabelview)
"frmSubSearchResults". Put it in the recordsource property of the subform
(change the tabelname and fields to your situation):

SELECT [Field1] & ";" & [Field2] & ";" & [Field3] & ";" & [Field4] AS
SearchFor, tblYourTable.Field1, tblYourTable.Field2, tblYourTable.Field3,
tblYourTable.Field4
FROM tblYourTable
WHERE ((([Field1] & ";" & [Field2] & ";" & [Field3] & ";" & [Field4]) Like
"*" & [Forms]![frmSearch]![txtSearchFor] & "*"));


Code behind the click event of the "SearchNow" button on the main form
"frmSearch" should be at least something like this:

Private Sub cmdSearchNow_Click()
Forms![frmSearch]![frmSubSearchResults].Requery
End Sub


Create a textbox "txtSearchFor" on your mainform "frmSearch".

That's it, Good luck!

Sid.

I have created a query that looks at nine columns of the same type of data

in my database. Every time I want to search for something else, I have to
go and change each criteria for each column. I would like to be able to
have a device (a form maybe) where the search criteria could be inputted,
the change made to the query, query ran and results returned all at once.