ACC97: ASP Query Cannot Be Used with the LIKE Predicate

• Microsoft Access 97
• Microsoft Internet Information Server version 3.0

SYMPTOMS

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

When you browse to an Active Server Pages (ASP) file that was exported from Microsoft Access 97, the Web browser either returns no records or you receive the following error message:

  Expression cannot be used with the LIKE predicate in query expression.

CAUSE

Because ASP files use ActiveX Data Objects (ADO) to communicate with ODBC drivers in order to query the back-end data, the SQL statements they contain have different character requirements than typical Microsoft Access SQL statements. ASP files use the percent sign (%) as a wildcard character in SQL statements whereas Microsoft Access uses the asterisk (*).

In addition, when Microsoft Access exports a query that contains a parameter concatenated with a wildcard, the SQL statement that is generated does not contain the correct sequence and number of delimiters around the wildcard.

RESOLUTION

Edit the SQL statements in the ASP files so that they use the percent sign (%) for wildcards.

If parameter queries with wildcards are exported, edit the SQL statements in the ASP file so that they use the percent sign (%) as the wildcard along with the correct sequence and number of delimiters around the wildcard.

MORE INFORMATION

NOTE: This section contains information about editing ASP files, and assumes that you are familiar with editing ASP files. Microsoft Access Technical Support engineers do not support customization of any HTML, HTX, IDC, or ASP files.

The following example demonstrates how to change the SQL Statement in an ASP file so that it contains the appropriate wildcard character and the correct sequence and number of delimiters around the wildcard.

  1. In Microsoft Access 97, open the sample database Northwind.mdb.

  2. Create the following new query called FindName based on the Customers
     table:

       Query: FindName
       ---------------
       Type: Select Query

       Field: CompanyName
       Table: Customers
       Criteria: Like [EnterName] & "*"

  3. On the Query menu, click Parameters.

  4. Type the following in the Query Parameters dialog box, and then click
     OK:

       Parameter          Data Type
       ----------------------------
       [EnterName]        Text

  5. Save the FindName query and close it.

  6. Select the FindName query in the Database window, and then click
     Save As/Export on the File menu.

  7. In the Save As dialog box, click "To an External File or Database,"
     and then click OK.

  8. In the "Save Query 'FindName' In" dialog box, select Microsoft Active
     Server Pages (*.asp) in the Save As Type box, and type FindName.asp in
     the File Name box. Note the folder where the files will be exported
     to. Click Export. The Microsoft Active Server Pages Output Options
     dialog box appears.

  9. In the Data Source Name box, enter the name of a System DSN that
     points to the sample database Northwind.mdb.

     For more information on how to define a system DSN, search the Help
     index for "ODBC, setting up data sources," and see the following
     article in the Microsoft Knowledge Base:

       ARTICLE-ID: Q159682
       TITLE:      "Data Source Name Not Found" Err Msg Opening Web Page

 10. In the Server URL box, enter the URL that points to the Web Server
     location where your ASP files will be stored. For example, if you
     store the ASP files in the \ASPsamp folder on the \\PubTest server,
     type http://pubtest/aspsamp/ as your Server URL. Click OK.

 11. Click OK in the Enter Parameter Value dialog box that appears. Note
     that the ASP output creates two files: FindName.HTML and FindName.ASP.

 12. Copy FindName.HTML and FindName.ASP to a folder on your Web Server
     computer where you have both Read and Execute permission. Read
     permission is necessary to browse the HTML file, and execute
     permission is necessary to run the ASP file.

     For more information about configuring Microsoft Internet Information
     Server (IIS) permissions, please refer to the IIS Help Index, and see
     the following article in the Microsoft Knowledge Base:

       ARTICLE-ID: Q160754
       TITLE:      Error "HTTP/1.0 403 Access Forbidden" Browsing IDC Page

 13. Use Notepad or another text editor to open the FindName.ASP file. You
     need to change the SQL Statement so that it will use the appropriate
     sequence of parameter and wildcard characters. Change the SQL
     Statement so that it looks as follows:

     sql = "SELECT Customers.CompanyName From Customers WHERE
     (((Customers.CompanyName) Like '" & Request.QueryString("[EnterName]")
     & "%'))"

     Note that the wildcard is a percent sign (%) sign and the sequence in
     which the delimiters were concatenated has changed.

 14. Save the FindName.ASP file and close it.

 15. Start Microsoft Internet Explorer 3.0 or another Web browser program.

 16. Type the Uniform Resource Locator (URL) in the address box of your Web
     browser to view FindName.ASP. For example, if you saved your ASP file 
     and your HTML file in a folder called Test in the wwwroot folder of 
     your Web Server, type:

        http://<servername>/test/FindName.html

     Note that the URL depends upon where your files are located on the Web
     Server.

 17. The FindName.HTML form opens in your Web browser with an [EnterName]
     box and a Run Query button. Type the letter "M" (without the quotation
     marks) in the box, and then click the Run Query button. Note that all
     records with names that begin with "M" are returned.

REFERENCES

For more information about exporting ASP files, search the Help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant. In addition, please refer to your ASP online documentation.

Keywords          : IntAsp kberrmsg kbinterop
Version           : 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbbug
Solution Type     : kbworkaround