Microsoft KB Archive/163181

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

Article ID: 163181

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q163181



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



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

 Follow steps 1-3 in the "Steps to Reproduce Behavior" section. Open the qryExport query in Design view.  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')  Follow steps 6-9 in the "Steps to Reproduce Behavior" section. View the results in a Web browser.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Open the sample database Northwind.mdb. Create a new query and add the following tables to the query: Orders, Order Details, and Products.</li>  Add the following fields to the QBE grid:

<pre class="fixed_text">     Table Name          Field name(s) -      ---      Orders              OrderDate Order Details      UnitPrice, Discount Products           Discontinued </li> Run the query, taking care to note the formatting of the fields, and then save the query as qryExport.</li> On the File menu, click Save As/Export.</li> In the Save As dialog box, click "To an External File or Database," and then click OK.</li> 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.</li> In the Save As Type box, click Microsoft IIS 1-2, and then click Export.</li> In the HTX/IDC Output Options dialog box, specify the following, and then click OK:

<ol style="list-style-type: lower-alpha;"> An HTML Template, if you want Microsoft Access to merge a template with the HTML extension (HTX) file.</li> 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.</li> A user name and password, if required to open the database.</li></ol> </li> Open the file qryExport.IDC from a Web browser.</li></ol>

Note the difference in the format of the data from step 3.

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.

<div class="references_section">