Knowledge Base Articles » KB100219: SQL Wild Cards, ADO and Microsoft Access.
If you are using wild card characters in a SQL query, and you are bewildered by the fact that the same SQL query only works in either Microsoft Access or ASP through ADO, but never both, then it is good to know that this is because ADO and DAO (the data engine behind Microsoft Access) recognize different sets of wild card characters.| Match Any String | Match Any Character | |
|---|---|---|
| ADO | % | _ |
| DAO | * | ? |
For example, if there is a table named 'Inventors' that looks like this:
| ID | FirstName | LastName | DOB |
|---|---|---|---|
| 1 | Alexander | Bell | 3/3/1847 |
| 2 | Thomas | Edison | 2/11/1847 |
| 3 | Wilbur | Wright | 4/16/1867 |
| 4 | Orville | Wright | 8/19/1871 |
Then, to search for all records of inventor whose last name starts with 'Wr' in Microsoft Access, the correct SQL query is:
SELECT * FROM Inventors WHERE LastName LIKE 'Wr*'
And, to search for all records of inventor whose last name starts with 'Wr' in ASP through ADO, the correct SQL query is:
SELECT * FROM Inventors WHERE LastName LIKE 'Wr%'
