ACC97: Queries Exported to HTX/IDC Appear with Different Format
ID: Q163181
|
The information in this article applies to:
-
Microsoft Access 97
-
Microsoft Internet Information Server versions 2.0, 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:
- 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.
MORE INFORMATIONSteps to Reproduce Behavior
- Open the sample database Northwind.mdb. Create a new query and add the
following tables to the query: Orders, Order Details, and Products.
- Add the following fields to the QBE grid:
Table Name Field name(s)
------------- -------------------
Orders OrderDate
Order Details UnitPrice, Discount
Products Discontinued
- Run the query, taking care to note the formatting of the fields, and
then save the query as qryExport.
- On the File menu, click Save As/Export.
- In the Save As dialog box, click "To an External File or Database,"
and then click OK.
- 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.
- In the Save As Type box, click Microsoft IIS 1-2, and then click
Export.
- In the HTX/IDC Output Options dialog box, specify the following, and
then click OK:
- An HTML Template, if you want Microsoft Access to merge a template
with the HTML extension (HTX) file.
- 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.
- A user name and password, if required to open the database.
- Open the file qryExport.IDC from a Web browser.
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.
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:
Q160754 Error "HTTP/1.0 403 Access Forbidden Browsing IDC Page
Additional query words:
kbinternet HTML IDC export primary key IIS PWS peer personal web server
Keywords : IsmIea
Version : 97
Platform : WINDOWS
Issue type : kbprb
|