ACC2000: Access C/S Does Not Display Full Results with COMPUTE (199137)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q199137
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you run a stored procedure that contains COMPUTE / COMPUTE BY, in a Microsoft Access project, the results are truncated.

RESOLUTION

To display sums, use a Microsoft Access report instead.

For additional information about using sums in reports, please see the following articles in the Microsoft Knowledge Base:

208850 ACC2000: How to Sum a Calculation in a Report

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Microsoft SQL Server Query Analyzer, run the following SQL in the sample database Northwind. This code creates the MyCompute stored procedure:

    CREATE Procedure MyCompute
    AS
    SELECT customerid, freight
    FROM orders
    WHERE customerid LIKE 'AN%'
    ORDER BY customerid, freight
    COMPUTE SUM(freight) by customerid
    COMPUTE SUM(freight)

  2. In Query Analyzer, run the following statement:

    EXEC MyCompute

    Note that the output pane shows the following:
       customerid freight               
   ---------- --------------------- 
   ANATR      1.6100
   ANATR      11.9900
   ANATR      39.9200
   ANATR      43.9000
   
              sum
              ===================
              97.42
   
   ANTON      4.0300
   ANTON      15.6400
   ANTON      22.0000
   ANTON      36.1300
   ANTON      47.4500
   ANTON      58.4300
   ANTON      84.8400
   
              sum
              ===================
              268.52
   
              sum
              ===================
              365.94
   
(14 row(s) affected)
  3. Open a project connected to the Northwind database.
  4. Run MyCompute. Note that in the output pane, you only see the following:
       customerid    freight
   ---------------------
   ANATR         $1.61
   ANATR         $11.99
   ANATR         $39.92
   ANATR         $43.90
Note that nearly two thirds of the information displayed in Query Analyzer does not show in the Microsoft Access project.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbbug KbClientServer kbpending KB199137
©2004 Microsoft Corporation. All rights reserved.