Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 208425 - Last Review: June 29, 2004 - Revision: 2.0
ACC2000: Type Mismatch Error Specifying Criteria with Count in a Query
This article was previously published under Q208425
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
When you specify text criteria for a Text field with
Count in the
Total row of a totals query, you may receive the following error message:
Data type mismatch in criteria expression.
The text criteria is being compared to the result of the
Count function, which is numeric.
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:
- Start Microsoft Access and open the sample database Northwind.mdb.
- In the Database window, click Queries under Objects, and then click New to create a new query based on the Orders table.
- Click the Totals button on the toolbar.
- Add the field ShipName to the query grid, and then select Count on the Total row.
- 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:
- Start Microsoft Access and open the sample database Northwind.mdb.
- In the Database window, click Queries under Objects, and then click New to create a new query based on the Orders table.
- 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.
- 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.
- Run the query and note that it returns the correct count.
Steps to Reproduce the Behavior
- Start Microsoft Access and open the sample database Northwind.mdb
- In the Database window, click Queries under Objects, and then click New to create a new query based on the Orders table.
- Drag the ShipName field to the first column of the QBE grid.
- Type Quick-Stop in the Criteria row.
- Click the Totals button on the toolbar, and then select Count in the Total row of the QBE grid.
- Run the query. Note that you receive the "type mismatch" error.
For more information about using the Count() function in queries, click
Microsoft Access Help on the
Help menu, type
count property in the Office Assistant or
the Answer Wizard, and then click
Search to view the topics
returned.
APPLIES TO
- Microsoft Access 2000 Standard Edition
| kberrmsg kbprb kbusage KB208425 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate