'Data type mismatch in criteria expression' error in Count query


A

Al

Hi,
I'm new to Access so be gentle with me ;-) I'm currently doing a project
where I need to count the number of cities (in a 'City' field) which meet
particular city names. I've done a count query with the city name in the
criteria, but all I get is an error saying 'Data type mismatch in criteria
expression'. I'm not sure what I'm doing wrong as the City field is text and
I'm typing the city name correctly.
Any help would be appreciated.
Thanks
Al
 
Ad

Advertisements

J

John Spencer

AS a WILD GUESS, you are typing the City name under the field you are
counting. Counts are numbers, cities are strings.

Remove the criteria under the count
Add the City field to the query AGAIN
Change GROUP BY to WHERE
Enter the city criteria under this copy of the city field.

It would help if you posted the SQL statement that you are attempting to
use. To do so, switch from DESIGN view to SQL view (Menu: View: SQL)
and copy the SQL statement and post it to the forum.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

KenSheridan via AccessMonster.com

Another wild guess! Have you used the 'lookup wizard' for the data type
when creating the field in the table in question? If so, although you see
text in the field, its data type will in fact be a number which references a
numeric primary key of a separate 'Cities' table. If so you'll need to join
the tables on the two numeric fields, and then include the text field from
the 'referenced' Cities table and enter the city name as the criterion for
this column. In SQL your query would look something like this:

SELECT CityName, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName = "New York"
GROUP BY CityName;

where YourTable is the current 'referencing' table, cities is the
'Referenced' table with one row per city, CityID is the name of the two
fields on which the tables are joined, and CityName is the name of the text
field in the Cities table.

If you want to include more than one city in the criteria you can use the IN
operator:

SELECT CityName, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName IN("New York", "San Francisco", "Chicago")
GROUP BY CityName;

Bear in mind that city names can be duplicated, so you could be counting two
cities as one. This is one reason why you should use a numeric value as the
keys as there can then be more than one row in Cities with the same CityName
but different numeric CityID values. You can then group a query on the
CityID as well as CityName columns to distinguish them, in which case you'd
probably also include a field such as State from the Cities table in the
result table:

SELECT Cities.CityID, CityName, State, COUNT(*) AS CityCount
FROM Cities INNER JOIN YourTable
ON Cities.CityID = YourTable.CityID
WHERE CityName IN("New York", "San Francisco", "Chicago")
GROUP BY Cities.CityID, CityName, State;

BTW if you have used the 'lookup wizard' se the following for reasons not to:

http://www.mvps.org/access/lookupfields.htm

Ken Sheridan
Stafford, England
 
Ad

Advertisements

A

Al

Thanks very much John. That was the answer I needed. I thought it would be
simple, it was just a matter of finding the answer.

Thanks to all who responded.
Cheers
Al

PS. sorry for the double post. I keep getting a 'Service Temporarily
Unavailable' error which caused it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top