ACC97: IDC Parameter Queries Cannot Use LIKE and Wildcards

• Microsoft Access 97

SYMPTOMS

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

When you browse to an IDC file that was exported from Microsoft Access 97, the Web browser returns the following error:

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

CAUSE

Because IDC files 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. IDC files use the percent sign (%) as a wildcard character whereas Microsoft Access uses the asterisk (*). When Microsoft Access exports a query that contains a parameter concatenated with an asterisk, the SQL statement that is generated contains a parameter concatenated with a percent sign. However, the IDC SQL statement does not need the concatenation operator (&). Instead, it simply needs a series of percent signs (%) to be placed after the parameter.

RESOLUTION

Edit the SQL statement in the IDC file so that it doesn't use a concatenation operator, but instead uses the appropriate sequence of percent signs (%).

MORE INFORMATION

NOTE: This section contains information about editing IDC files, and assumes that you are familiar with editing IDC 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 IDC file so that it contains the appropriate sequence of parameter and wildcard characters.

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

  2. Create a 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 IIS
     1-2 (*.htx;*.idc) in the Save As Type box, and type FindName.htx in
     the File Name box. Note the folder where the files will be exported
     to. Click Export. The HTX/IDC Output Options dialog box appears.

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

     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. Click OK in the Enter Parameter Value dialog box that appears.

 11. The HTX/IDC output creates three files: FindName.HTML, FindName.htx,
     and FindName.IDC.

 12. Copy FindName.HTML, FindName.htx and FindName.IDC 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 IDC 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.IDC 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 like this:

      SQLStatement:SELECT Customers.CompanyName
      +FROM Customers
      +WHERE (((Customers.CompanyName) Like '%[EnterName]%%%'));

 14. Save the FindName.IDC 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.HTML. For example, if you saved your IDC
     files 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) into the box, and then click the Run Query button.
     All records with names that begin with "M" are returned.

REFERENCES

For more information about exporting IDC files, search the Help Index for "IDC files," and then choose "Export a datasheet to dynamic HTML format," or ask the Microsoft Access 97 Office Assistant. In addition, please refer to the IIS Help Index.

For more information about using wildcards in IDC files, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147361
   TITLE     : How to Use Wild-Cards in IDC Files