ACC: Type Mismatch Specifying Criteria on Count in Query

Last reviewed: May 7, 1997
Article ID: Q151867
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

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:

   Type Mismatch

CAUSE

The Text criteria is being compared to the result of the Count which is numeric.

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. The following example uses the sample database Northwind.mdb (or NWIND.MDB in version 2.0) to demonstrate adding a field to a totals query twice.

  1. Open the sample database Northwind.mdb (NWIND.MDB in Microsoft Access version 2.0) and create a new query based on the Orders table.

  2. Click the Totals button on the toolbar.

  3. Add the field ShipName to the query and select Count on the Total row.

  4. 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.

NOTE: In Microsoft Access 2.0, there is a space in the Ship Name field.

Method 2

Use two queries. The following example demonstrates how to build a totals query based on a select query with a Text criteria:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0) and create a new query based on the Orders Table.

  2. 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.

  3. 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.

  4. Run the query and note that it returns the correct count.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0). Create a new query based on the Orders Table.

  2. Drag the ShipName field to the first column of the QBE grid.

  3. Type "Quick-Stop" in the Criteria row.

  4. Click the Totals button on the toolbar, and then select Count in the Total row of the QBE grid.

  5. Run the query. Note that you receive the "Type Mismatch" error.

REFERENCES

For more information about using the Count() function in queries, search the Help Index for "Count," and view the available topics, or ask the Microsoft Access 97 Office Assistant.


Keywords : kberrmsg kbusage QryTotal
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.