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

The information in this article applies to:

Microsoft Access 2002

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 "About ANSI SQL query mode" topic in the Microsoft Access Help system has a hyperlinked subtopic named "Why use ANSI-92 SQL?" 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.

MORE INFORMATION

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.
Click to select the This database check box to enable ANSI 92 syntax for this database.

In SQL view of a new query, copy and paste the following statement:

SELECT count (distinct companyname) AS Expr1
FROM Customers;

Try to save the query. Note that you receive the error message that is mentioned in the "Symptoms" section of this article.

REFERENCES

For more information about the Help topic discussed in this article, click Microsoft Access Help on the Help menu, type about ansi sql query mode in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Modification Type:	Major	Last Reviewed:	10/20/2003
Keywords:	kbbug kbdocerr kberrmsg kbnofix KB306250