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

can you post some raw data and expected result in text here?

we can try write some sql for your reference.

if some data is null,just covert it to 0 before sum.

sql may like below

select f,sum(iif(f2 is null,0,f2)) from t group by f

And show sql may more clear than QBE window screenshot which you provided .

Answer 2

Hi,

Your Query Expression,

Total: Total

Lot Qty: Total

These 2 seems to be the problem.

Can you change one of the "Total" as the expression because the query cannot have the same column names. Say, "Lot Qty: Total" to "Lot Qty: TotalQty".

Answer 3

Yes, I have one of them renamed as Lot Qty, and the other I left as Total. One of them is the sum of the value and the other is counting it.
Having both bits of information is very valuable.

Answer 4

I will try to explain this to the best of my ability. This is the raw data once imported into Access.

What I am trying to do is create a Query that I can export into Excel that has a line for each unique item (if there are multiples then for them to be consolidated) so I can have a list of what was sold.

This next screenshot is of a past set of info that I did when it worked after its exported into excel. (I have since added "Lot Qty" Column.

For the SQL, I am not sure how to get to more info than this for the query.

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;

Answer 5

Kind of hard to really see what's the problem.

Try to do by removing all the GroupBy Fields except one to do a test until you find the cause.

Since you don't have any ItemID as an Index, this condition may throw an error. So, try to remove other fields except "Item Description" or any unique field.