ACC97: Queries Exported to HTX/IDC Appear with Different Format

• Microsoft Access 97
• Microsoft Internet Information Server 2.0 and 3.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you export a table or query to the HTX/IDC web format, some of the fields' data formatting is lost. This will happen if any of the fields in the file you are exporting is formatted in one of the following data types:

• Currency
• Date/Time
• Percent
• True/False - Yes/No - On/Off

CAUSE

The Format property of tables in Microsoft Access is used for display purposes only. When you export data from Microsoft Access, the actual data in the field is exported. Dollar signs ($), and percent symbols (%) are not exported. Date fields are exported as defined by the international settings on your computer. Yes/No fields are exported as -1 for Yes and 0 for No.

RESOLUTION

As a workaround to this behavior, you can format the data in your columns using the Format() function as follows:

1. Follow steps 1-3 in the "Steps to Reproduce Behavior" section.

2. Open the qryExport query in Design view.

3. Create expressions for each of the fields that need formatting. For example:

         Order Date: Format ([OrderDate], 'short Date')
         Unit Price: Format([Order Details].[UnitPrice],'Currency')
         Item Discount: Format([Discount],'Percent')
         Item Discontinued: format([Discontinued],'Yes/No')
   

4. Follow steps 6-9 in the "Steps to Reproduce Behavior" section.

5. View the results in a Web browser.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb. Create a new query and add the following tables to the query: Orders, Order Details, and Products.

2. Add the following fields to the QBE grid:

         Table Name          Field name(s)
         -------------       -------------------
         Orders              OrderDate
         Order Details       UnitPrice, Discount
         Products            Discontinued
   
   

3. Run the query, taking care to note the formatting of the fields, and then save the query as qryExport.

4. On the File menu, click Save As/Export.

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

6. In the "Save Query 'QryExport' in..." dialog box, specify the file name and location for the exported file.

   NOTE: These files must be saved or copied to a folder on an Internet Information Server or Personal Web Server that has Execute permissions established.

7. In the Save As Type box, click Microsoft IIS 1-2, and then click Export.

8. In the HTX/IDC Output Options dialog box, specify the following, and then click OK:

       a. An HTML Template, if you want Microsoft Access to merge a template
          with the HTML extension (HTX) file.
   

       b. The data source name you will use to query your Microsoft Access
          database when using dynamic web pages created for Microsoft
          Internet Information Server or Peer Web Servers.
   

       c. A user name and password, if required to open the database.
   

9. Open the file qryExport.IDC from a Web browser.

When you save a table, query, or a form datasheet as an Internet Database Connector file (IDC), Microsoft Access creates two files: an Internet Database Connector file and an HTML extension (HTX) file. These files are used to generate a Web page that displays current data from your database.

NOTE: You can also save a table or query as an IDC/HTX file by using the "Publish to the Web" Wizard.

REFERENCES

For more information about the requirements needed for executing HTX/IDC files on a Web browser, please see the following article in the Microsoft Knowledge Base:

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