You can build a Dynaset or Snapshot based upon wildcard field-searchcharacters in an SQL query. The find methods (FindFirst, FindLast,FindNext, and FindPrevious) can also search a Dynaset or Snapshot usingwildcard search characters in an SQL query.
By using the Like statement in the SQL query language, you can searchfor database field values using the asterisk (*) and question mark (?)characters as wildcards. The * and ? wildcards let you find a wider set offield values beginning or ending with any desired root. For example, thefollowing SQL syntax selects the records from a table where theAuthorfield 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, cannotuse SQL queries or wildcard search characters. The Seek method is limitedto finding a single record using the comparison operators: >, >=, <=, <, =,and <>.
Asterisk (*) Wildcard Usage
In the SQL syntax for the Like statement, the asterisk (*) acts as awildcard place holder for any number of characters, from zero up to thefield length. A search for b* finds any field value beginning with theletter 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 awildcard place holder for a single character. A search for ??b* finds anyfield value that has b in the third character. A query for *b?? finds anyfield 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 Print MyDS!author MyDS.MoveNext Wend
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 Print MyDS!author MyDS.FindNext SQLx 'Find next record matching criteria. Loop
Example Using SQL Wildcard Search with a Data Control
The Text1 box in the following program shows individual records of theAuthor field of the BIBLIO.MDB database. When you click the Command1button, the program automatically appends and prefixes the * wildcardsearch character to any search string that you enter in the Text2 text box.That widens the resulting recordset shown in Text1. You can browse therecordset 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. End Sub
- 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 database table:
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 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
- 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 canmodify 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 fastbut doesn't support wildcard searches. Seek is mainly useful for finding asingle record that matches a given criteria. The find and move methods aremore practical than the Seek method for finding a group of records.
The Seek method feature that is closest to a wildcard search is acomparison operator: >, >=, <=, or <. For example, you could find the firstrecord 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