Microsoft KB Archive/302411

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

Article ID: 302411

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q302411



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
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.



CAUSE
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 (*).



RESOLUTION
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 &quot;*&quot; syntax, alter the syntax to use Like &quot;%&quot;, 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 &quot;*&quot; . 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 &quot;%&quot;.
 * 8) Preview the data access page again, and note that the page now returns the correct records.

Additional query words: prb asterisk dap percent

Keywords: kbprb KB302411

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.