ACC2000: Type Mismatch Error Specifying Criteria with Count in a Query (208425)

RESOLUTION

You can use one of the following two methods to work around this behavior. These two methods assume that the criteria is meant to limit the records before the Count is performed by the query.

Method 1

You can add the field to the query a second time, and use Where on the Total row. To add a field to a totals query twice, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. In the Database window, click Queries under Objects, and then click New to create a new query based on the Orders table.
3. Click the Totals button on the toolbar.
4. Add the field ShipName to the query grid, and then select Count on the Total row.
5. Add the field ShipName to the query again. Select Where on the Total row. On the Criteria row, type Quick-Stop. Run the query and note that it returns the correct count.

Method 2

Use two queries. To build a totals query based on a select query with a text criteria, follow these steps:

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. In the Database window, click Queries under Objects, and then click New to create a new query based on the Orders table.
3. Drag the ShipName field into the first column of the QBE grid. Type Quick-Stop in the Criteria row. Save the query as Ship Name 1.
4. Create another new query based on the Ship Name 1 query. Drag the ShipName field to the QBE grid in the first column. Click the Totals button on the toolbar, and then select Count in the Total row.
5. Run the query and note that it returns the correct count.