ACC97: Format Properties Ignored When Exporting Queries to ASP

ID: Q163014

The information in this article applies to:
  • Microsoft Access 97
  • Microsoft Internet Information Server version 3.0

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.
  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:
    Q159682 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/ 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:
    Q160754 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.


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.
  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:
    Q159682 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/ 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:
    Q162975 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.



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.


REFERENCES

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 : WINDOWS:97; winnt:3.0
Platform : WINDOWS winnt
Issue type : kbprb


Last Reviewed: November 3, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.