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.
MORE INFORMATION
Steps to Reproduce Problem
- Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access version 2.0).
- 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. - 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. - Click Options and select (or type) Pubs in the Database box, and click OK.
- 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. - While in the Database window, click the Queries tab.
- Click New, and then click OK.
- In the Show Table dialog box, click Close.
- On the View menu, click SQL View (or SQL in Microsoft Access versions 2.0 or 7.0).
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);
- On the Query menu, click Run. Note that the query incorrectly returns all of the records.
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
Additional query words: prb sub-select
Keywords: kbbug kbinterop KB171948