Knowledge Base

ACC2000: Report Based on Query Has More Records Than the Query

Article ID: 197108

Article Last Modified on 12/12/2002

Microsoft Access 2000 Standard Edition

This article was previously published under Q197108

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you print or preview a report that is based on a query, it may contain more records than the query returns.

CAUSE

You may encounter this problem when the following conditions are true:

You create a report based on a query to an ODBC data source.

-and-
The MaxRecords property is set for that query.

Queries based on ODBC tables (such as Linked SQL Server tables) can use the MaxRecords property to limit the number of records that are returned from the ODBC data source. However, when you create a report based on a query that has the MaxRecords property set, the report ignores the MaxRecords property and returns all the rows from the data source.

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 Problem

Create a data source to the SQL Server Pubs database.
On the File menu, point to Get External Data, and then click Link Tables.
In the File Of Type list, click ODBC Databases. Click Link.
Select the data source created in step 1. Click OK.
Click dbo.authors, and then click OK.

Create the following query, and then save it as qryAuthors:

   Query: qryAuthors
   ---------------------------
   Type: Select Query

   Field: Last Name: au_lname
      Table: dbo_authors
      Sort: Ascending
      Field: First Name: au_fname
         Table: dbo_authors

Set the MaxRecords property of the qryAuthors query to five.
On the Query menu, click Run.

Note that the result contains only five records.
Click the qryAuthors query. On the Insert menu, click AutoReport.

Note that the report contains all records, not just the first five.

REFERENCES

For more information about the MaxRecords property, click Microsoft Access Help on the Help menu, type maxrecords property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Keywords: kbbug kbdta kbpending KB197108