In this article our guest author Bernhard Keel and myself (Andrew Couch) look at how to leverage the power of the often overlooked BuildCriteria function in formulating your SQL Clauses. In the last article Art60_DynamicSearch.aspx we showed how to unleash the power of unbound forms when working with SQL Server. This article extends that work and looks at a pure Access solution to getting clever searching and improved performance. The techniques could also be adapted to work with SQL Server.

The article introduces the Application.BuildCriteria function as an effective and flexible method for building filters, and then leads on to describe a unique approach to searching by entering criteria in a main form and several subforms.

BuildCriteria

If you have been building filters or using where clauses in VBA, you will appreciate that you need to construct the SQL according to a set of rules. Here are a few simple examples.

The SQL in Access to look for records with a field ShippedDate equal to 15-Aug-1994, then we need to write the date in US date format and add # symbols around the date:-

ShippedDate=#8/15/1994#

If we want to filter on a text value then we need to include the text string in single or double quotes, for example to search an OrderNotes field for the string ,Shipping arranged using 1, we would require to use :-

OrderNotes="Shipping arranged using 1"

Once you start to need to add ranges, then you need expressions like the following:-

ShippedDate Between #8/15/1994# And #8/30/1994#

To wildcard matching you will need expressions such as:-

OrderNotes Like "*shipping*"

If you want a user to be able to search on a date range, you will probably want to add two fields, one to provide a start date, and another to provide the end date. When it comes to wildcards you will normally decide to add these through code.

BuildCriteria can make your life very much simpler, because it will do two things for you. Firstly if you tell it the data type it will perform the formatting, adding the # symbols, double quotes and like keyword. The function takes three parameters, fieldname, datatype and control value; in the example below we assume that our controlname matches the underlying fieldname.

strWhereClause = strWhereClause & _
            BuildCriteria(ctrl.Name, dbDate, ctrl.Value)

Secondly when typing in search criteria it will allow you to use terms such as Between. So if you type into an unbound control which is called ShippedDate as shown below:-

BuildCriteria will return:-

ShippedDate Between #8/15/1994# And #8/30/1994#

For a text field you can type in the wildcards as shown below:-

BuildCriteria will return:-

OrderNotes Like "*ship*"

If you type in the following in an unbound field called Quantity, which is numeric :-

BuildCriteria will return:-

Quantity>1 And Quantity<12

Some other examples to consider are the following, for example on a text field called Account:-

Input	Result
<> MM1500	Account<>"MM1500"
Null	Account Is Null
like MM5* or MM2*	Account Like "MM5*" Or Account Like "MM2*"
MM5* and MM2*	Account Like "MM5*" And Account Like "MM2*"

If you type into multiple fields, then you will still need the programming logic to add AND, OR criteria between the statements for each control.

We have chosen to use the Tag property, on each unbound control to hold a number indicating the data type to be entered in the unbound control:-

We have adopted the following convention (which you could easily extend):-

• 1 = numerical (double, long, integer....)
• 2 = string
• 3 = date (any date format)
• 4 = boolean
• 10= ignore control
• 20= calculated field to be hidden when searching

We can then construct the following function which will build a string that can be used as a filter or SQL where clause (we have also added a Replace function to double up any single quotes in text data):-

Public Function BuildFormSubformWhereClause(frm As Form) As String
  Dim ctrlname As String
  Dim ctrl As Control
  Dim strWhereClause As String
  strWhereClause = ""
  For Each ctrl In frm.Controls
    With ctrl
      If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox _
          Or TypeOf ctrl Is CheckBox Or TypeOf ctrl Is OptionGroup _
          Or TypeOf ctrl Is ToggleButton Or TypeOf ctrl Is ListBox Then
        If .Tag <> 10 And .Tag <> 20 Then
          If Nz(.Value) <> "" Then
          If strWhereClause <> "" Then
            strWhereClause = strWhereClause & " AND "
          End If
          ctrlname = "[" & frm.Tag & "].[" & .Name & "]"
          Select Case .Tag
            Case 1: strWhereClause = strWhereClause & _
              BuildCriteria(ctrlname, dbLong, .Value)
          Case 2: strWhereClause = strWhereClause & _
            BuildCriteria(ctrlname, dbText, Replace(.Value, "'", "''"))
          Case 3: strWhereClause = strWhereClause & _
            BuildCriteria(ctrlname, dbDate, .Value)
          Case 4: strWhereClause = strWhereClause & _
            BuildCriteria(ctrlname, dbBoolean, .Value)
          End Select
          End If
        End If
      End If
    End With
  Next ctrl
  BuildFormSubformWhereClause = strWhereClause
End Function

Searching with multiple subforms

In our previous article Art60_DynamicSearch.aspx we demonstrated how a single form could be unbound for entering search criteria, and then bound to show the results. In this article we will extend this to allow search criteria to be entered in multiple subforms in addition to the main form.

Our sample form is shown below, which displays related information in two subforms:-

If we wanted to search for all orders where the shipping date was between 3-Oct-1995 and 10-Oct-1996, but only where the order notes contained the word ship, and only for the product Chang. Then we would unbind the form and subforms (pressing the Blank button) and enter the following search criteria:-

The full code behind these operations can be downloaded at the end of this article. The result of our function which uses BuildCriteria is three SQL strings:-

For the main form we have:-

ShippedDate Between #10/3/1995# And #10/10/1996#

For the product details subform we have:-

ProductID=2

For the notes subform we have:-

OrderNotes Like "*ship*"

Note, the download code adds square brackets and a prefix for the table name which is not shown here, such that ShippedDate would become [orders].[ShippedDate].

Pressing the Search button we get the following results:-

To get this result we want to apply all three of the earlier search filters against the main form:-

ShippedDate Between #10/3/1995# And #10/10/1996# AND ProductID=2 AND OrderNotes Like "*ship*"

And apply to the product details subform the filter:-

ProductID=2

Also apply to the order notes subform the filter:-

OrderNotes Like "*ship*"

Searching Technique, the Dynamic RecordSource and DISTINCTROW

The first point to note is that this method can be used to allow search criteria to be applied in a subform, which is then used to limit all the master records that have subform records meeting the required criteria.

For example if we only enter search criteria in the product subform, such as the following:-

We need to limit the data in the main form to only include those orders which have the specified product on them.

Rather than basing the main form on the Orders table, we will need to base it on a query, which also includes the tables used in the subforms, and also uses an outer join to allow for situations where records are not present in the subforms and no subform criteria is specified:-

Normally this kind of query for a master record has a problem, in that if we include the child records we will get multiple records:-

In the SQL we can use the DISTINCT keyword to remove duplicates, which can be achieved by setting the following query property (Unique Values):-:-

This then shows the following result:-

The problem with using this is that the query is read only.

Access however, has an alternative property which will remove the duplicates and make the query read/write ( in the SQL this is the DISTINCTROW keyword), the property is shown below:-

Using this we will get an updateable recordset, which hides the duplicates:-

The SQL is as shown below:-

We now know how to construct the main form recordsource to remove duplicates and yet include the related tables for filtering. Looking back now to the filter criteria shown below :-

ShippedDate Between #10/3/1995# And #10/10/1996# AND ProductID=2 AND OrderNotes Like "*ship*"

We have a problem, because we cannot try and do the following (because qryOrders does not expose the fields on the subforms:-

SELECT * FROM qryOrders WHERE ShippedDate Between #10/3/1995# And #10/10/1996#
AND ProductID=2 AND OrderNotes Like "*ship*"

We want to apply this filter against the SQL used by the master record, so we need to extract the actual SQL being used in the qryOrders, and build a valid SQL string for filtering the records:-

SELECT DISTINCTROW orders.*
FROM (orders LEFT JOIN OrderDetails ON orders.OrderID = OrderDetails.OrderIID)
LEFT JOIN OrderNotes ON orders.OrderID = OrderNotes.OrderId
WHERE ShippedDate Between #10/3/1995# And #10/10/1996#
AND ProductID=2 AND OrderNotes Like "*ship*"

To do this in our program code we use the following code snippet (strMainFormWhereClause contains the search criteria and strMainFormQuery = "qryOrders"):-

  strSQL = CurrentDb.QueryDefs(strMainFormQuery).SQL
  strSQL = Replace(strSQL, ";", "")
  strSQL = strSQL & " WHERE " & strMainFormWhereClause

If you look at our main form, you will see the following assignment for the Tag property:-

We also have a naming convention for our subforms, which are called SubForm1, SubForm2…etc, and the Tag property in each subform, contains the name of the table used for the subform:-

• SubForm1 , Tag : OrderDetails
• SubForm2, Tag : OrderNotes
• MainForm, Tag: Orders

SubForm Records and Search Criteria

If you specify search criteria in a subform, we understand that it will cause the main form to only filter those orders with child records matching the criteria, but what should be shown in the subform?

Normally a subform will show all the child records, but here we have chosen to make the subform also filter down the records that it will show. The simplest solution would be to filter the main form and ignore any filtering in the child subforms, but the code we have will also restrict what is shown in child subforms. So as you page through the main form records each subform is also restricted by the search criteria.

The code snippet that we use to cycle through the subforms is as follows:-

  'Set the recordsource for the subforms
  For lngSubForm = 0 To lngSubforms - 1
    strSubForm = "SubForm" & lngSubForm + 1
    strSQL = "SELECT * FROM " & _
      frm.Controls(strSubForm).Form.Tag
    If strSubFormWhereClause(lngSubForm) <> "" Then
      strSQL = strSQL & " WHERE " & strSubFormWhereClause(lngSubForm)
    End If
    ' set the recordsource for the subform
    frm.Controls(strSubForm).Form.RecordSource = strSQL
  Next

Because we do not need to apply criteria across multiple tables (which was the case for the main form), we can simplify the SQL we need to construct for the subforms (where TableQueryName is the value extracted from the tag):-

SELECT * TableQueryName WHERE Criteria

Working with Datasheets and Hiding Fields

Sometimes we have a field which we only want to display when showing results, but not search against, we assigned these a Tag value of 20. If these fields are displayed in a subform in continuous view, then you can use the ctrl.Visible = True/False to show or hide the fields, but if you are going to have the continuous form shown in DataSheet view then you will find that this does not work.

Rather than use this property you need to manipulate the ctrl.ColumnHidden = True/False property, but there is a catch in that controls which are in a footer or header will be visible to program code when you cycle through the controls, but will generate an error if you try and set the ColumnHeader property (because they are not shown when displaying the continuous form in DataSheet View). For this reason we have incorporated an On Error Resume Next to pass on those errors:-

  If ctrl.Tag = 20 Then
    ctrl.Visible = False
    ' add an error trap as this applies to columns only in datasheet
    ' view and not in footer or header
    On Error Resume Next
    ctrl.ColumnHidden = True
  End If

We could have used the following to avoid the need for using On Error Resume Next:-

    If ctrl.Section = 0 Then
      ctrl.ColumnHidden = True
    End If

Limitations

One limitation of switching between bound and unbound fields is the use of formatting and input masks, both of which for example in a currency field like Freight would mean that entering an exact match such as 1.0 would work, but entering > 1.0 would not work, because the formatting would prevent that. Although not a major setback this is worth noting.

One work around here would be to encode the formatting in either an array, or in the Tag property for each control (which would mean coding to hold multiple values in a single Tag), then remove the formatting during searching, and reapply it when showing results.

Another work around would be to extend the Tag classification, for example a value of 6 indicates a Currency field, and then apply the formatting to all such fields before displaying the results.

Summary

In this article we have shown both the power of the BuildCriteria function to take the pain out of correctly building filters and offer users a simple method to enter ranges and wildcards in search criteria. Also we have demonstrated a unique approach to searching with a form and subforms that can easily switch between a bound and unbound situation, and allow entering search criteria in the subforms.

This approach of switching between bound and unbound forms for searching is a very powerful technique and we hope you will enjoy your experiences with these methods.

Download Code

The sample code can be downloaded from here for Access 2007/2010 Databases.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page