ACC: Query with Subquery Returns Incorrect Result

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

A query containing 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

1. Open the sample database Northwind.mdb (or NWIND.MDB if you are using 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:

   ARTICLE-ID: Q114678
   TITLE     : How to Create and Use Subqueries