ID: Q163014
The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.
When you browse to an ASP file that was exported with Microsoft Access 97, the Format property of fields in Microsoft Access tables or queries is not preserved.
When tables or queries are exported to ASP format, Microsoft Access 97 generates an ASP file that retrieves and displays the data from the record source, but the export functionality is not designed to preserve format properties. Numbers will be displayed in General Number format, Dates will be displayed as General Date, Times will be displayed in Long Time format, and Yes/No fields will be displayed in True/False format.
There are two possible workarounds for preserving formats:
This example uses the sample database Northwind.mdb.
1. Create the following query based on the Order Details table. Name it
qryFormatTest. For each of the formatted field(s) you are trying to
export, you must create a calculated field in the query using the
Format() function as indicated below.
Query: qryFormatTest
------------------------------------------------
Field: OrderID
Criteria: <10300
Field: ProductID
Field: NewPrice: Format([UnitPrice], "Currency")
Field: Quantity
Field: NewDiscount: Format([Discount],"0%")
2. Save and close the query. Select the query in the Database window.
3. On the File menu, click Save As/Export.
4. In the Save As dialog box, click to select "To an External File or
Database," and click OK. Note that the "Save Query 'qryFormatTest'
In" dialog box appears.
5. In the Save As Type box, select Microsoft Active Server Pages (*.asp)
and type qryFormatTest.asp in the File Name box. Note the folder where
the files will be exported to. Click Export. Note that the Microsoft
Active Server Pages Output Options dialog box appears.
6. In the Data Source Name box, enter the name of a System DSN that
points to the sample database Northwind.mdb.
For more information about 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 : ACC97: "Data Source Name Not Found" Err Msg Opening Web
Page
7. 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/" (without the quotation marks) as your
Server URL. Click OK. The ASP output creates the file
qryFormatTest.asp.
8. Copy qryFormatTest.asp to a folder on your Web Server computer where
you have Execute permission. 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 : ACC97: Error "HTTP/1.0 403 Access Forbidden" Browsing
IDC Page
9. Start Microsoft Internet Explorer 3.0, or another Web browser program.
10. Type the Uniform Resource Locator (URL) in the address box of your Web
browser to view qryFormatTest.ASP. For example, if you saved your ASP
file in a folder called Test in the wwwroot folder of your Web Server,
type:
http://<servername>/test/qryFormatTest.ASP
Note that the URL depends upon where your files are located on the Web
Server.
11. Note that the NewPrice and NewDiscount fields have formatting applied.
NOTE: This section contains information about editing ASP files and assumes that you are familiar with editing HTML files, Active Server, and Visual Basic Scripting. Microsoft Access Product Support professionals do not support customization of any HTML, HTX, IDC, or ASP files.
This example uses the sample database Northwind.mdb.
1. Create the following query based on the Order Details table. Name it
qryFormatTest.
Query: qryFormatTest
--------------------
Field: OrderID
Criteria: <10300
Field: ProductID
Field: UnitPrice
Field: Quantity
Field: Discount
2. Save and close the query. Select the query in the Database Window.
3. On the File menu, click Save As/Export.
4. In the Save As dialog box, click to select "To an External File or
Database," and click OK. Note that the "Save Query 'qryFormatTest'
In" dialog box appears.
5. In the Save As Type box, select Microsoft Active Server Pages (*.asp)
and type qryFormatTest.asp in the File name box. Note the folder where
the files will be exported to. Click Export. Note that the Microsoft
Active Server Pages Output Options dialog box appears.
6. In the Data Source Name box, enter the name of a System DSN that
points to the sample database Northwind.mdb.
For more information about 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 : ACC97: "Data Source Name Not Found" Err Msg Opening Web
Page
7. 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/" (without the quotation marks) as your
Server URL. Click OK. The ASP output creates the file
qryFormatTest.asp.
8. Use Notepad or another text editor to open the qryFormatTest.asp file.
Towards the bottom of the file you will see the following code which
is a combination of HTML and Active Server Scripting:
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("UnitPrice").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Quantity").Value)%>
<BR></FONT ></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Discount").Value)%>
<BR></FONT></TD>
To format the UnitPrice field as Currency and the Discount field
as Percent, you must modify the code so it uses the VB Script
FormatCurrency and FormatPercent functions:
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(formatcurrency(rs.Fields
("UnitPrice").Value))%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Quantity").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(formatpercent(rs.Fields
("Discount").Value,0))%><BR></FONT></TD>
Please refer to your VB Script Language Reference available in the ASP
online documentation for more information about the VB Script Format
functions.
9. Copy qryFormatTest.asp to a folder on your Web Server computer where
you have Execute permission. 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: Q162975
TITLE : ACC97: Permissions Necessary to View HTML, IDC, and ASP
Files
10. Start Microsoft Internet Explorer 3.0, or another Web browser program.
11. Type the Uniform Resource Locator (URL) in the address box of your Web
browser to view qryFormatTest.ASP. For example, if you saved your ASP
file in a folder called Test in the wwwroot folder of your Web Server,
type:
http://<servername>/test/qryFormatTest.ASP
Note that the URL depends upon where your files are located on the Web
Server.
12. Note that the UnitPrice and Discount fields have formatting applied.
Method 2 may be a better choice because the output will have right- justified Currency fields so the decimal point appears in the same position throughout the column. Method 1 will output the field as left-justified Text which may not line up the decimal point in the same position for each record. The disadvantage with Method 2 is that VB Script has the FormatCurrency, FormatNumber, FormatDateTime, and FormatPercent functions, but does not have a Format function where custom formats can be supplied.
For more information about exporting tables or queries to ASP, search the Help Index for "ASP files," and then "Export a datasheet to dynamic HTML format."
Additional query words: HTML IDC ASP export primary key IIS PWS Peer personal web server
Keywords : kbinterop
Version : 97
Platform : WINDOWS
Hardware : x86
Issue type : kbprbLast Reviewed: May 20, 1999