How to Use Wildcards in SQL Query to Make Dynasets & Snapshots

ID: Q110069

3.00 WINDOWS

The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

You can build a Dynaset or Snapshot based upon wildcard field-search characters in an SQL query. The find methods (FindFirst, FindLast, FindNext, and FindPrevious) can also search a Dynaset or Snapshot using wildcard search characters in an SQL query.

MORE INFORMATION

By using the Like statement in the SQL query language, you can search for database field values using the asterisk (*) and question mark (?) characters as wildcards. The * and ? wildcards let you find a wider set of field values beginning or ending with any desired root. For example, the following SQL syntax selects the records from a table where the Authorfield field values begin with the letter b:

   Select * from XTable Where Authorfield Like 'b*'

NOTE: The Seek method, which only applies to Table object variables, cannot use SQL queries or wildcard search characters. The Seek method is limited to finding a single record using the comparison operators: >, >=, <=, <, =, and <>.

Asterisk (*) Wildcard Usage

In the SQL syntax for the Like statement, the asterisk (*) acts as a wildcard place holder for any number of characters, from zero up to the field length. A search for b* finds any field value beginning with the letter b. A search for *b finds any field value ending with b. A search for *xxxx* finds any field value that contains the xxxx substring. A search for * by itself matches all field values.

Question Mark (?) Wildcard Usage

In the SQL syntax for the Like statement, the question mark (?) acts as a wildcard place holder for a single character. A search for ??b* finds any field value that has b in the third character. A query for *b?? finds any field value with b as the third from the last character.

Speed Considerations

Of the following two techniques, 1 is faster than 2:

1. For greater speed, invoke the SQL wildcard field search only once to

   build the Dynaset or Snapshot of records that match your search
   criteria. Then use the fast move methods (MoveFirst, MoveLast,
   MoveNext, and MovePrevious) or click the data control to quickly
   navigate between all the records that match the specified search
   criteria. For example:

   Dim MyDS As Dynaset, MyDB As database, SQLx As String
   SQLx = "Select * from Authors Where Author Like 'b*' "
   Set MyDB = OpenDatabase("BIBLIO.MDB")   'Open a database.
   Set MyDS = MyDB.CreateDynaset(SQLx)     'Create Dynaset using SQLx.
   While Not MyDS.Eof
      Print MyDS!author
      MyDS.MoveNext
   Wend

   The Eof property is True after MoveNext moves past the last record.

   Visual Basic creates a Dynaset or Snapshot very quickly when using
   indexes. Subsequent find methods are relatively slow and sequential, as
   shown in technique 2 below.

2. A slower technique is to create a Dynaset composed of the entire table

   and then to use multiple find methods. Each FindNext would re-invoke the
   SQL wildcard field search to find the next matching record. This adds
   query time overhead. After finding a certain number of records, the
   total time taken would be slower than with technique 1 described above.

   Dim MyDS As Dynaset, MyDB As Database, SQLx As String
   SQLx = "author Like 'b*'"
   Set MyDB = OpenDatabase("BIBLIO.MDB")     'Open a database.
   Set MyDS = MyDB.CreateDynaset("Authors")  'Create Dynaset with table.
   MyDS.FindFirst SQLx      'Find first record matching criteria.
   Do Until MyDS.NoMatch
      Print MyDS!author
      MyDS.FindNext SQLx    'Find next record matching criteria.
   Loop

   You can invoke the FindNext method until Nomatch = True, as shown.

Example Using SQL Wildcard Search with a Data Control

The Text1 box in the following program shows individual records of the Author field of the BIBLIO.MDB database. When you click the Command1 button, the program automatically appends and prefixes the * wildcard search character to any search string that you enter in the Text2 text box. That widens the resulting recordset shown in Text1. You can browse the recordset shown in Text1 by clicking the data control.

1. Start Visual Basic or begin a New Project. Form1 is created by default.

2. Double-click the form. Add the following to the Form Load event code:

   Sub Form_Load ()
      text1.Text = "Enter ar* in Text2 and click Command1. Also try *z* "
      text2.Text = "*"  'A lone asterisk finds all records.
   End Sub

3. Add a data control (Data1) to Form1.

4. Add a text box (Text1) to Form1. Give Text1 the following properties in

   order to bind it to the data control and to the Author field in the
   database table:

   DataSource = Data1
   DataField = Author

5. Add a second text box (Text2) without setting any properties. You can

   change the wildcard criteria for database queries in Text2 at run time.

6. Add a command button (Command1) to Form1. Add the following code to its

   Click event:

   Sub Command1_Click ()

      Dim SQLX As String, SearchText As String

      'Optional: In Text2, append & prefix the * wildcard to widen search:
      If Right$(text2.Text, 1) <> "*" Then text2.Text = text2.Text & "*"
      If Left$(text2.Text, 1) <> "*" Then text2.Text = "*" & text2.Text
      'Remove the above 2 lines if you want the user to enter the asterisk
      SearchText = text2.Text

      ' The following SQL syntax selects all records from the Authors table
      ' where the Author field matches the SearchText string, using any *
      ' or ? wildcard characters. The result is ordered by the Au_id field:
      SQLx = "Select * From Authors Where Author Like '" & SearchText
      SQLx = SQLx & "' Order By Au_id"
      Data1.DatabaseName = "biblio.mdb" ' Tells Data1 the database name.
      Data1.RecordSource = SQLx ' Data1 control will use SQLx query string.
      Data1.Refresh ' Update the data control with results of SQL query.

   End Sub

7. Start the program by pressing the F5 key. When a lone asterisk (*) is in

   the Text2 box, clicking the Command1 button finds all the records.

   Enter ar in Text2 and click Command1. The program changes the query to
   *ar*. That finds all Author field values that contain the letters ar.

   Enter z or *z* and click Command1 to find all Author field values that
   contain the letter z anywhere in the field.

   Close the form to end the program.

To change the way the program automatically adds the * wildcard, you can modify or remove the If Left$... and If Right$... statements.

The Seek Method Does Not Support Wildcard Searches

The Seek method, which works only with Table object variables, is very fast but doesn't support wildcard searches. Seek is mainly useful for finding a single record that matches a given criteria. The find and move methods are more practical than the Seek method for finding a group of records.

The Seek method feature that is closest to a wildcard search is a comparison operator: >, >=, <=, or <. For example, you could find the first record that is greater than or equal to your search key value as follows:

   Dim MyDB As Database, MyTable As Table
   Set MyDB = OpenDatabase("BIBLIO.MDB")      ' Open a database.
   Set MyTable = MyDB.OpenTable("Publishers") ' Open a table.
   MyTable.Index = "PrimaryKey"               ' Define current index.
   MyTable.Seek ">=", 3         ' Seek a record with PrimaryKey >= 3.
   If MyTable.NoMatch Then
      MsgBox "match was not found"
   Else
      MsgBox "match was found"
   End If

Additional reference words: 3.00 faster slow speedy quick quicker KBCategory: KBSubcategory: APrgDataAcc

Keywords          : kbcode APrgDataAcc 
Version           : 3.00
Platform          : WINDOWS

Last Reviewed: May 22, 1998