MSQuery: "Can't Group By..." Error Adding Criteria

• Microsoft Query for Windows, version 1.0

SYMPTOMS

In Microsoft Query, when you add criteria to a query created using SQL Server data source, you receive one of the following error messages:

• Can't Group By Bit Field

  -or-

• TEXT and IMAGE datatypes may not be used in a GROUP BY clause

CAUSE

This behavior occurs when you add criteria to a query for a Bit field, Text field, or an Image field, AND you also add criteria for a grouped field.

The GROUP BY clause in an SQL statement cannot contain a Bit field, Text field, or Image field. You can use these fields in a HAVING clause or a WHERE clause; however, if you use one of these field types, along with a grouped field, in a HAVING clause, you receive one of the error messages above.

The HAVING statement is created when you add criteria to a query for a grouped field.

Note that you do not receive this error message when the Bit field, Text field, or Image field to which you added criteria is used in the data pane.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in in the version of Microsoft Query that ships with Microsoft Excel version 5.0c for Windows.

MORE INFORMATION

You can group records in a query by selecting the Group Records check box in the Query Properties dialog box, or by displaying totals for a field. Both of these operations create the GROUP BY clause in the SQL statement. The GROUP BY clause combines records with identical values in the specified field list into a single record.

REFERENCES

For more information about Data Types (Advanced), choose the Search button in Help and type:

   image data type