DevGuru Knowledge Base Articles

Quick References

Features

Partners

A100219: 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%'

The Guru