Handle case-insensitive searches using the SearchExpression method.

Summary

The "expression" argument for the SearchExpression method requires an SQL WHERE clause formatted string in order to return an appropriately filtered Recordset. By default, when querying string fields, the value included in the WHERE clause must be a string that has characters which match the case of the characters in the Recordset.

For example, "STATE_NAME = 'new jersey'" will not retrieve the New Jersey record if the value in the STATE_NAME field is stored in all upper case, or proper case; for example, "New Jersey".

This document provides two possible solutions for building case-insensitivity into an application.

Procedure

• Option 1
  
  If it is known that the string values in the data tables will always be either lower-case, upper-case, or proper-case, but it is not known which case it will be for any particular record, build a WHERE clause along with OR operators to capture the correct record.
  

  Code:
  strField = txtFieldName.Text
  strValue = txtQueryValue.Text
  strWhere = strField & " = '" & strValue & "' OR " & _
              strField & " = '" & UCase(strValue) & "' OR " & _
              strField & " = '" & LCase(strValue) & "' OR " & _
              strField & " = '" & StrConv(strValue, vbProperCase) & "'"
  Set recs = mlyr.SearchExpression(strWhere)

  
  
• Option 2
  
  Manipulate the data table so that all string values in all string fields are stored in all UPPER case. Then the application can convert the input string before querying. Also, the user can enter their value into the 'txtQueryValue' text box in any case combination they want, and the query will be successful.
  

  Code:
  strField = txtFieldName.Text
  strValue = UCase(txtQueryValue.Text)
  strWhere = strField & " = '" & strValue & "'"
  Set recs = mlyr.SearchExpression(strWhere)