Search Records Using SQL Statements, Including Multiple-Criteria Searches

Applies Criteria as RECORDSOURCE, Eliminates "Query Clutter"

Preface: what I was trying to do was design a search form and have the user be able search the records by "product type." They would also be able to FURTHER narrow the records down by a date range. Moreover, I wanted them to be able to check a box on the "search form" called "all dates," in which case it would search by "product type" but NOT by date range. Today, I figured out how to do this, in large part from tips I derived from this page (it's listed on my home page as an "other helpful links," the link called http://www.wa.apana.org.au/~abrowne/)

Assume the search form is called search_form. The form to be viewed with the results is called browse_all_form. The dates are entered in text boxes on the search form, their names are beg_date_2 and end_date_2. The "product_type" field where the user enters their criteria is called prod_type_entered. The check box on the search is called all_dates_box. If it is checked, then dates are NOT counted in the filter; if it is NOT checked, dates ARE counted in the filter IN ADDITION to "product type."

First, you would create regular queries which search for this data. The query which DID consider the dates and the query which does NOT consider the dates are 2 separate queries. Create them, then view them in SQL view. Paste the resulting code in a text editor, and keep them separate.

Then, this is the code which is assigned to the "search" button on the search form as an "on click" event:

Dim sSQL As String ' Larry R Harrison Jr LarryTucAZ@yahoo.com If (Me.all_dates_box = False) Then ' Filter by Dates too sSQL = "SELECT main_table.* FROM main_table WHERE (((main_table.date_closed) Between [Forms]![search_form]![beg_date_2] And [Forms]![search_form]![end_date_2]) AND ((main_table.product_type)=[Forms]![search_form]![prod_type_entered]) AND ((main_table.void)=False));" ' It's probably best to keep the above statement as 1 line, syntax is very picky DoCmd.OpenForm "browse_all_form" Forms![browse_all_form]!StatusBox = "Dates were considered" Forms![browse_all_form].Form.RecordSource = sSQL Else ' DON'T Filter by Dates, just by Product Type sSQL = "SELECT main_table.* FROM main_table WHERE (((main_table.product_type)=[Forms]![search_form]![prod_type_entered]) AND ((main_table.void)=False));" ' It's probably best to keep the above statement as 1 line, syntax is very picky DoCmd.OpenForm "browse_all_form", Forms![browse_all_form]!StatusBox = "Dates WERE NOT CONSIDERED" Forms![browse_all_form].Form.RecordSource = sSQL End If

Obviously, you would substitute your own form and field names throughout the code (as well as the "sSQL" portion); otherwise, you can copy it as is.

Notice that the sSQL portion of the code is merely a variable set equal to the SQL code derived from the "design view" contents of the original queries when they were viewed in SQL view. The SQL code which you earlier pasted in the text editor would then be pasted in the Visual Basic Code.

It's important to document this in comment lines. Mainly, describe what the normal queries look like so that someone else with a more beginner's level approach can later debug any peculiarities. Otherwise, once this is done, the original queries can safely be deleted altogether.

Which does bring up a good point. One thing about the above code is that is allows you to not have so many queries in the database window. They can quickly add up. At the same time, I have seen databases before where the reports (which were based on queries) didn't work quite as they were supposed to, and having normal queries to refer back to & debug was much easier than the above code would be, especially to someone who's more at the beginner/intermediate level. Keep that in mind. Use of the above code is at a rather advanced level. So you may decide to use the above code for some functions and regular queries for others.

Multiple Criteria Searches

Today, AT LAST, I solved a long-standing issue--how to design a search form which searches by multiple criteria at the same time, and that automatically senses which fields you've entered criteria for, and varying the SQL statements to suit this. In this scenario, there would also only be 1 search button to handle the entire affair, rather than having 1 search button (and separate SQL statements to go with them) for each possibility. Following is an example and the code I used.

Assume you're going to search from a form named "search_form_XYZXYZ", then you will open a form named "browse_xyz" to show the results of your search. It is pulling these searches from the tables "main_ship_table" and "customer_table." The 1st table is the "MAIN" table, and it contains a field named "ship_to_id" which relates to the "autoid" field from the 2nd table. The "descriptive" field from the 2nd table is renamed "Ship To Name" in the query/SQL statements.

Further assume that you wish to be able to search by "Shipped To" name, or "date shipped" or BOTH, and you want the form to automatically detect null values and ignore searching by null values, but to search by that criteria if something was entered. The following code would be applied to the "on click" event of the "search" command button.

The initial sSQL= are the SQL statements that assume a "show all records" viewpoint. I designed the query and BEFORE I put it any searching criteria/assumptions, I extracted the SQL statement-equivalents of it, and that is how I initially set-up the sSQL= expression. I then add on any extras in the form of "AND" statements using the If...then loops.

Then, since ending any SQL statement with "AND" produces a syntax error--and that is what happens if any search criteria are entered--I used the If Right(sSQL,3)=" ND" statements; I search for the existence of it and, if it's there, remove it courtesy of the sSQL = Left(sSQL, sLen - 4) statement within the loop.

Another Example of Searching by Multiple Criteria

In this example, there are 2 tables, Sheet1 and Software. The 1st table is a "parent" table, the 2nd a "child table." The 2nd table contains a list of software installed on each machine (the 1st table is a table of machines). This example requires 2 fundamentally different queries. Both are shown here, with the SQL statement equivalents:

	SELECT DISTINCT Sheet1.* FROM Sheet1;
	SELECT Sheet1.*, Software.Software FROM Software LEFT JOIN Sheet1 ON Software.computerid = Sheet1.ID; The Join Arrow States Include ALL records from "Software" and only those records from "Sheet1" where the joined fields are equal

The search form searches for the following fields: machine, software, os system, RAM. Except for software, all of those fields are in the main "sheet1" table, while the software field is derived from the "software" table. The design of the "software" table is as follows:

It becomes necessary to distinguish these queries from each other because it affects how the Visual Basic is coded. In the previous example, as you enter more search criteria, you are only tacking extra statements in the SQL. In this example, that also applies unless you're searching for the "software" field, which is in the other Software table. In that case, the "earlier" portions" of the SQL is entirely different, though the "later" portions of the SQL (starting with WHERE) would be the same. That explains why the coding is different in this case.

The coding applied to the "on click" event of the SEARCH button follows:

Again, as the user enters in more search criteria, the portions of the SQL statement coming after "WHERE" are the same no matter what. The parts of the SQL which would change if they include the software field in their search would be the portion of the SQL coming BEFORE the "WHERE." That is why the sSQL3 holds the portion of the SQL coming after WHERE--the portion which retains the same basic layout throughout--so that it can be kept up with no matter what search criteria the user selects. Notice that in the portion of the code which checks to see if the user is searching by software (the code is color-coded in white), the sSQL variable assumes the layout of the sSQL2 variable in the earlier portion of the expression, then tacks on sSQL3 to assume the "trailer" portion of the code whose basic layout remains unchanged.