ACC2000: Data Access Page Based on Query That Uses Jet Wildcard Characters Returns No Results

When you create a data access page that is based on a query whose criteria use Microsoft Jet wildcard characters, the page does not return any records.

This behavior occurs because Jet-specific wildcard characters are not valid for data access pages, and data access pages therefore do not return records. Data access pages use the Microsoft Jet OLEDB provider, which recognizes only ANSI 92 wildcard characters. The query functions properly if you run the query from Access, but a data access page that is based on the same query returns no records.

Because data access pages use ActiveX Data Objects (ADO) to communicate with ODBC drivers to query the back-end data, the SQL statements that the data access pages contain have different character requirements from those of typical Access SQL statements. For example, data access pages use the percent sign (%) as a wildcard character in SQL statements, whereas Access uses the asterisk (*).

To resolve this issue, alter the syntax of the query to use the ANSI 92 wildcard character equivalents. For example, if the query uses a Like "*" syntax, alter the syntax to use Like "%", save the query, and then run the query again. The query returns no records unless you actually have literal percent signs in your data, but when you run the data access page that is based on the query, the data access page displays all records.

Steps to Reproduce the Behavior

1. Open the sample database Northwind.mdb.
2. In the database window, open the Current Product List query in Design view.
3. In the criteria row, under Product Name, type Like "*" . Save and close the query.
4. In the database window, under Objects, click Pages and then click New.
5. In the New Data Access Page dialog box, click AutoPage: Columnar, click Current Product List under Choose the table or query where the object's data comes from, and then click OK.
6. Preview the data access page, and note that it returns no records.
7. Open the Current Product List query again, and modify the criteria to read Like "%".
8. Preview the data access page again, and note that the page now returns the correct records.