ACC97: Format Properties Ignored When Exporting Queries to ASP

• Microsoft Access 97
• Microsoft Internet Information Server 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:

        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.

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 Technical Support engineers 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.

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