Untangling the Search Strings

DoCmd.FindRecord
This method is the one most sensitive to what characters are embedded in a text search criteria. With this method, you'll need to watch out for #, [ ], |, ", and '. Going back to my client's problems, in order to get the search routines to work with their odd company names, I had to differentiate between using # as a wildcard and using it as a literal character. When used as a literal character, the # must be enclosed between brackets -- [#].

There are several ways to make the search criteria work with the #, but the solution depends on whether you're letting your users enter search criteria through an InputBox or through a complete form. If you're using the InputBox function as the search criteria user interface in your application, then you should use the following code. This routine displays an InputBox and then checks for a # in the entered string. If the routine finds a #, the code uses the Instr function to find the characters before and after the #. The code then rebuilds the statement, enclosing the # in square brackets. Finally, the FindRecord method is used with the rebuilt criteria string:

Private Sub cmdSrchUsingMidFunction_Click() On Error GoTo Err_cmdSrchUsingMidFunction_Click Dim strCriteria As String Dim strCompany As String Dim strLeft As String Dim varMid As Variant Dim strRight As String Dim intLen As Integer Dim intChar As Integer strCompany = InputBox("Enter Company Name to Search for....") If strCompany = "" Then Exit Sub End If intLen = Len(strCompany) intChar = InS r(1, strCompany, "#", 1) If intChar > 0 Then strLeft = Left(strCompany, (intChar - 1)) strRight = _ Right(strCompany, (intLen - intChar)) varMid = Mid(strCompany, intChar, 1) strCriteria = strLeft & "[" & varMid & "]" & _ strRight GoTo NumberSignCriteria Else strCriteria = strCompany End If DoCmd.GoToControl "CompanyName" DoCmd.FindRecord strCriteria, , , , True GoTo Exit_cmdSrchUsingMidFunction_Click NumberSignCriteria: DoCmd.GoToControl "CompanyName" DoCmd.FindRecord strCriteria, , , , True Exit_cmdSrchUsingMidFunction_Click: Exit Sub Err_cmdSrchUsingMidFunction_Click: MsgBox Err.Description Resume Exit_cmdSrchUsingMidFunction_Click End Sub

If you can implement the use of separate forms to gather search criteria, the problem can be handled by including a combo box that lists the available search criteria. For this example, I created two forms: a data entry form called "frmCustomers" and a search form used to find company names, "frmSrchUsingColumns". In the data entry form, there's a command button that opens the search form when clicked (see Figure 1).

In the search form, there's a combo box called "cboCompanyName" that retrieves the CustomerID and CompanyName from the Customer table, though only the CompanyName is displayed because the CustomerID column has its width set to zero (see Figure 2). The CustomerId remains the bound column, however. The user can select the problematic CompanyName from the combo box, but you can extract the more consistent CustomerID for the selected entry and use it for your searches. The code that goes behind the Command button that triggers the search looks like this:

Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click Dim strCriteria As String strCriteria = Me!cboCompanyName If strCriteria = "" Then Exit Sub End If DoCmd.Close DoCmd.SelectObject acForm, "frmCustomers" DoCmd.GoToControl "CustomerID" DoCmd.FindRecord strCriteria Exit_cmdOK_Click: Exit Sub Err_cmdOK_Click: MsgBox Err.Description Resume Exit_cmdOK_Click End Sub

The other benefit of this code is that the CustomerID field is probably an indexed Autonumber field, which this method searches almost instantly.

When both [ and ], or |, or "" are embedded in a search criterion, the Jet database engine won't evaluate the search string, and you won't get a match for your search criteria. When just a [ is used with DoCmd.FindRecord, you'll get a runtime error. Ironically, if only the ] is embedded in the search criteria, the Jet database engine will have no trouble dealing with it, and you'll find the correct match.

There's a simple solution to this problem: Use SearchAsFormatted in the FindRecord method by setting the FindRecord method's fifth parameter to True:

DoCmd.FindRecord strCriteria, , , , True.

0Recordset Find methods
As a personal opinion, I believe using these Recordset Find methods is more effective and useful than the DoCmd Find methods. Besides letting you use NoMatch, the Jet database engine has no problems recognizing most of the different characters that might be embedded in a search criterion.

First, let's take a quick look at concatenation with quotation marks. When submitting a query to Jet, you can use either single or double quotes to enclose strings (other database engines might require you to use double quotes). If you use double quotes in your string, you must double them up. Either of these statements creates a valid criteria string:

strCompany = "Name" 'gives [CompanyName]='Name' strCriteria = "[CompanyName] ='" & strCompany & "'" 'gives [CompanyName="Name" strCriteria = "[CompanyName] =""" & strCompany & """"

The good news is that if you use single quotation marks, Access will deal with double quotation marks within the search criteria (for example, Harry "Buster" Crabbe). However, if the search criteria contain single quotes (for instance, B's Beverages), you'll get a runtime error. Switching to doubled-up double quotes for your string concatenation reverses the problem: no problem with single quotes, syntax errors for double quotes embedded in the criteria.

One obvious solution to the problem is to use double quotes if the string includes a single quote and single quotes if the criteria include double quotes. Here's a typical example of the code that you could use:

Private Sub cmdTwoSrchStrings_Click() On Error GoTo Err_cmdTwoSrchStrings_Click Dim rst As Recordset Dim strCriteria As String Dim strCompany As String Dim intChar As Integer Set rst = Me.RecordsetClone strCompany = _ InputBox("Enter Company Name to Search For") intChar = InStr(1, strCompany, "'", 1) If strCompany = "" Then Exit Sub ElseIf intChar > 0 Then strCriteria = "[CompanyName]=""" & _ strCompany & """" Else strCriteria = "[CompanyName] ='" & _ strCompany & "'" End If rst.FindFirst strCriteria If rst.NoMatch Then MsgBox "No Match Was Found" Exit Sub End If Me.Bookmark = rst.Bookmark rst.Close Exit_cmdTwoSrchStrings_Click: Exit Sub Err_cmdTwoSrchStrings_Click: MsgBox Err.Description Resume Exit_cmdTwoSrchStrings_Click End Sub

The other problem I've found with the Recordset Find methods is when the | character is embedded in the search criteria: You'll get a runtime error when the search runs. I have no idea why somebody would want to use this character in a database field, and there doesn't seem to be any way to handle this character. It's up to you to decide how to deal with the problem -- either don't let users search for this character or prevent the character from entering your database.

SQL statements
The Microsoft Jet database engine evaluates SQL statements in the same way it evaluates the Recordset object's Find methods. So when you build SQL statements in VBA code, you should use the same techniques that I discussed previously for the Recordset Find methods.

As I've noted, though, different SQL dialects have different rules, and you'll need to make adjustments to your SQL statements. If you're using ODBC to connect your application in a client/server environment, you must be aware of the SQL dialect being used by the database engine located on the server side of your network.

Now that these particular search strings are untangled, it's up to you to decide how to apply the solutions in your own applications. If you can anticipate these problems before you complete an application, you can make use of input masks and validation rules in your application's tables to control the data that can be input. I personally eliminate the use of the | character, as well as the use of [ ]. All other characters have a practical use for data entry and, as I've shown, they can be handled relatively easily with VBA code to prevent problems.

Download STRINGS.exe

Gabriel Moreno is president of GDS Data Systems in San Diego, CA. His company services specific industry fields, including the commercial and residential building industry and the product manufacturing and distribution industry. Gabriel has been a software developer for more than five years. GDS Data Systems' preferred developing tools are MS Visual Studio and MSOffice Developer Edition. marmolsd@aol.com.