ACC2002: Data Access Pages Cannot Filter Records When Certain Words Are Used (275071)
The information in this article applies to:
This article was previously published under Q275071 Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
SYMPTOMS
When you try to filter a data access page, you notice that sometimes the filter works as expected and sometimes it does not. It appears as though the filter never works when you use certain words to filter the data.
CAUSE
The wildcard character for SQL Server (the percent sign (%)) together with hexadecimal values are used as escape code delimiters by HTML engines when they parse a Universal Resource Locator (URL).
RESOLUTION
Encode the percent sign as a separately encoded value. In other words, change the character % to %25. When the HTML engine sees the encoded value %25, it interprets the value to be the equivalent of %.
For example, change the following URL
http://<ServerName>/Customers.htm?ServerFilter="Notes LIKE '%Alfreds%'"
to:
http://<ServerName>/Customers.htm?ServerFilter="Notes LIKE '%25Alfreds%25'"
where <ServerName> is the name of your Web server.
MORE INFORMATION
URLs typically use standard ASCII characters, but they may also contain characters that do not have a corresponding "display-able" character in a character set. These types of characters must be encoded. For example, if you type a URL that contains a space, before the page can be retrieved, the space is encoded to the three characters %20. This is what typically appears in the Address box in Microsoft Internet Explorer instead of the literal space.
To encode certain characters, URLs use the % character, which is the same character as the wildcard character for SQL Server. Any encoded characters in a URL that are represented by the percent sign are also followed by a two-digit hexadecimal value. Hexadecimal values are made up of the numbers 0 through 9 and the letters "a" through "f."
This means that any server filter that contains the % character as a wildcard character that is followed by a hexadecimal values (0 through 9 and "a" through "f") will not work. Only alpha characters "g" through "z" that follow the % character will work. For example, %G% will work, but %F% will not.
In the example in the "Steps to Reproduce the Behavior" section, a server filter includes %a as part of its string. The HTML engine interprets this as an escape code instead of as a SQL Server wildcard character followed by the letter "a". The string %Alfreds% is not completely passed to the server filter property of the Microsoft Office Data Source Control (MSODSC). The resulting filter instead appears as CompanyName LIKE '
lfreds%' (note the space before the letter "l").
The following example reproduces the problem that is described in this article. The steps assume that your Access 2002 computer also has a Microsoft certified Web server installed on the same computer. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database. Steps to Reproduce the Behavior- Open the sample database Northwind.mdb.
- Create a new data access page that is based on the Customers table. Save the page as Customers.htm to your Web server under C:\Inetpub\Wwwroot.
- In Microsoft Internet Explorer, browse to this page by using a URL similar to:
http://<ServerName>/Customers.htm?ServerFilter="CompanyName LIKE '%Alfreds%'"
where <ServerName> is the name of your Web server. Note that no records are returned, even though the word "Alfreds" is in the Company Name field for Customer ID ALFKI. - Remove the letter "a" from the word "Alfreds" in the URL, and then press ENTER. Note that the record for Customer ID ALFKI is returned.
| Modification Type: | Minor | Last Reviewed: | 9/27/2006 |
|---|
| Keywords: | kbfix kbprb KB275071 |
|---|
|