Access Data Type Mismatch Error

Question

Hi I have an Access Database that has worked well in the past and it now has an error I can't figure out how to fix. The error reads "Data Type Mismatch in Criteria Expression"

Please help me figure this out.

Answer 1

In addition to John's concerns about generic fields with "Short Text" datatype, I see a number of aggregates in that SQL, which are likely to be the source of the current problem. If you try to sum a field or calculate on a field with a null in one record, that's the error you get. I really do encourage you to look at the data in addition to the SQL. It may not be the source of the problem. But unless you rule it in -- or rule it out -- we can't know for sure.

Answer 2

Looks like a table without a PK. That's a bad idea.

Also too many text fields where a more appropriate data type like Currency or Date/Time should be used.

Answer 3

What do you mean my PK?

Answer 4

SELECT [Pieces/Minifigs/Sets].Condition, [Pieces/Minifigs/Sets].[Item Description], Sum([Pieces/Minifigs/Sets].Qty) AS Qty, Sum([Pieces/Minifigs/Sets].Total) AS Total, Count([Pieces/Minifigs/Sets].Total) AS [Lot Qty], [Pieces/Minifigs/Sets].[Item Type], [Pieces/Minifigs/Sets].[Item Number]

FROM [Pieces/Minifigs/Sets]

GROUP BY [Pieces/Minifigs/Sets].Condition, [Pieces/Minifigs/Sets].[Item Description], [Pieces/Minifigs/Sets].[Item Type], [Pieces/Minifigs/Sets].[Item Number]

ORDER BY Count([Pieces/Minifigs/Sets].Total) DESC;

I understand that the datatypes could be better I am only after a small percentage of this info. I am trying to consolidate the information down to these 7 columns and I think those datatypes are correct. Maybe I'm wrong but thought that the others didn't have to be correct.

Answer 5

The data its looking at is like this.
I am trying to consolidate the items to get the seven columns of data I want.

If you know of a better way to consolidate the info, I'm all ears.