Microsoft KB Archive/163014

= ACC97: Format Properties Ignored When Exporting Queries to ASP =

Article ID: 163014

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition
 * Microsoft Internet Information Server 3.0

-



This article was previously published under Q163014



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



CAUSE
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.



RESOLUTION
There are two possible workarounds for preserving formats:

Method 1: Using the Format Function in a Query
This example uses the sample database Northwind.mdb.   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%")  Save and close the query. Select the query in the Database window. On the File menu, click Save As/Export. 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. 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. 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:

159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page

</li> 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/ as your Server URL. Click OK. The ASP output creates the file qryFormatTest.asp.</li> 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:

160754 ACC97: Error "HTTP/1.0 403 Access Forbidden" Browsing IDC Page

</li> Start Microsoft Internet Explorer 3.0, or another Web browser program.</li> 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:// /test/qryFormatTest.ASP

Note that the URL depends upon where your files are located on the Web Server.</li> Note that the NewPrice and NewDiscount fields have formatting applied.</li></ol>

Method 2: Modifying the ASP File Using VB Script
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. <ol>  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 </li> Save and close the query. Select the query in the Database Window.</li> On the File menu, click Save As/Export.</li> 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.</li> 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.</li> 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:

159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page

</li> 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/ as your Server URL. Click OK. The ASP output creates the file qryFormatTest.asp.</li>  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. </li> 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:

162975 ACC97: Permissions Necessary to View HTML, IDC, and ASP Files

</li> Start Microsoft Internet Explorer 3.0, or another Web browser program.</li> 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:// /test/qryFormatTest.ASP

Note that the URL depends upon where your files are located on the Web Server.</li> <li>Note that the UnitPrice and Discount fields have formatting applied.</li></ol>

<div class="moreinformation_section">

MORE INFORMATION
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.

<div class="references_section">