ACC: Query with Subquery Returns Incorrect Result (171948)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q171948
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

A query that contains a subquery that acts as criteria returns incorrect results.

CAUSE

The query is based on an ODBC data source, and the ODBC driver incorrectly interprets the data type of the subquery result as text.

WORKAROUND

There are two workarounds for this behavior:
  • Use a SQL pass-through query.
  • Try using a different SQL statement that would return the same result. For example, change the SQL statement 
        SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
    WHERE 1 < (SELECT COUNT(*) FROM dbo_authors AS a2
    WHERE a2.city = a1.city);

    to the following: 
        SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
    WHERE a1.city In (SELECT a2.city FROM dbo_authors AS a2
    GROUP BY a2.city HAVING (Count(a2.au_id)>1));

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 2.0, 7.0 and 97.

MORE INFORMATION

Steps to Reproduce Problem


  1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access version 2.0).
  2. On the File menu, point to Get External Data, and then click Link Tables.

    If you are using Microsoft Access version 2.0, click Attach Table on the File menu.
  3. In the Files Of Type list, click ODBC Databases() and select the SQL Server Data Source; provide the SQL Server Login information if necessary, and then click OK.

    If you are using version 2.0, select SQL Server in the Attach dialog box; click the SQL data source, provide the SQL Server Login information if necessary, and then click OK.
  4. Click Options and select (or type) Pubs in the Database box, and click OK.
  5. Select the Authors table and click OK.

    If you are using version 2.0, select the Authors table and click Attach. When you receive the message that the table has been successfully attached, click OK.
  6. While in the Database window, click the Queries tab.
  7. Click New, and then click OK.
  8. In the Show Table dialog box, click Close.
  9. On the View menu, click SQL View (or SQL in Microsoft Access versions 2.0 or 7.0).
  10. Type the following SQL statement, which should return only those records where the value in the City field occurs in another record as well: 
         SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
     WHERE 1 < (SELECT COUNT(*) FROM dbo_authors AS a2
     WHERE a2.city = a1.city);
  11. On the Query menu, click Run. Note that the query incorrectly returns all of the records.
  12. If you are using Microsoft Access version 7.0 or later, click SQL View (or SQL) on the View menu. Enclose the numeral 1 following the first instance of WHERE in quotation marks so that the SQL statement appears as follows: 
         SELECT DISTINCT a1.au_id FROM dbo_authors AS a1
     WHERE "1" < (SELECT COUNT(*) FROM dbo_authors AS a2
     WHERE a2.city = a1.city);

    Then run the query. Note that only those records where the value in the City field occurs in another record are returned. However, this SQL statement returns the error "Type mismatch" if it is run in Microsoft Access version 2.0.

REFERENCES

For more information about subqueries, search the Help Index for "subqueries" and display the topic "Use a subquery to define a field or define criteria for a field."

For additional information, please see the following article in the Microsoft Knowledge Base:

114678 How to Create and Use Subqueries
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbbug kbinterop KB171948
©2002 Microsoft Corporation. All rights reserved.