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.
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*'
: 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: >, >=, <=, <, =,
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.
Of the following two techniques, 1 is faster than 2:
- 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:
The Eof property is True after MoveNext moves past the last record.
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
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.
- 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.
You can invoke the FindNext method until Nomatch = True, as shown.
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
MyDS.FindNext SQLx 'Find next record matching criteria.
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.
- Start Visual Basic or begin a New Project. Form1 is created by default.
- 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.
- Add a data control (Data1) to Form1.
- 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
DataSource = Data1
DataField = Author
- Add a second text box (Text2) without setting any properties. You can
change the wildcard criteria for database queries in Text2 at run time.
- Add a command button (Command1) to Form1. Add the following code to its
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.
- 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"
MsgBox "match was found"
Article ID: 110069 - Last Review: January 9, 2003 - Revision: 1.1
- Microsoft Visual Basic 3.0 Professional Edition
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.