Microsoft KB Archive/171948

= ACC: Query with Subquery Returns Incorrect Result =

Article ID: 171948

Article Last Modified on 1/20/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



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.



Steps to Reproduce Problem
 Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access version 2.0).</li> 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.</li> 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.</li> Click Options and select (or type) Pubs in the Database box, and click OK.</li> 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.</li> While in the Database window, click the Queries tab.</li> Click New, and then click OK.</li> In the Show Table dialog box, click Close.</li> On the View menu, click SQL View (or SQL in Microsoft Access versions 2.0 or 7.0).</li>  Type the following SQL statement, which should return only those records where the value in the City field occurs in another record as well:

<pre class="fixed_text">    SELECT DISTINCT a1.au_id FROM dbo_authors AS a1     WHERE 1 < (SELECT COUNT(*) FROM dbo_authors AS a2     WHERE a2.city = a1.city); </li> On the Query menu, click Run. Note that the query incorrectly returns all of the records.</li>  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:

<pre class="fixed_text">    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. </li></ol>

<div class="references_section">