Microsoft KB Archive/306250

= ACC2002: Setting ANSI 92 Compatibility in a Database Does Not Allow DISTINCT Keyword in Aggregate Functions =

Article ID: 306250

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q306250



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

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
The &quot;About ANSI SQL query mode&quot; topic in the Microsoft Access Help system has a hyperlinked subtopic named &quot;Why use ANSI-92 SQL?&quot; This subtopic states the following:

You may want to use ANSI-92 SQL for the following reasons:

 * You want to take advantage of the new features not found in ANSI-89 SQL, such as:
 * Changing security settings by using the GRANT and REVOKE SQL statements
 * Using DISTINCT in an aggregate function reference, for example, SUM(DISTINCT Price)
 * Using the LIMIT TO nn ROWS clause to limit the number of rows returned by a query

However, when you try to use the following ANSI-92 SQL statement in a Microsoft Access database SELECT SUM(DISTINCT field1) from Table1 you receive the following error message:

Syntax error (missing operator) in query expression 'sum(distinct field1)'



RESOLUTION
Change the query as follows: SELECT Sum(Field1) FROM (SELECT DISTINCT Field1 FROM Table)



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce the Behavior
 Create a new database. On the File menu, point to Get External Data, and then click Import. Browse to and select the Northwind sample database, and then click Import. In the Import Objects dialog box, click the Customers and the Orders tables, and then click OK. On the Tools menu, click Options, and then click the Tables/Queries tab.</li> Click to select the This database check box to enable ANSI 92 syntax for this database.</li>  In SQL view of a new query, copy and paste the following statement: SELECT count (distinct companyname) AS Expr1 FROM Customers; </li> Try to save the query. Note that you receive the error message that is mentioned in the &quot;Symptoms&quot; section of this article.</li></ol>

<div class="references_section">