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

drop table PiecesMinifigsSets;
create table PiecesMinifigsSets (item_description text,qty double);
insert into PiecesMinifigsSets values('black title',0.11);
insert into PiecesMinifigsSets values('black title',null);
insert into PiecesMinifigsSets values('black title',0.11);
select * from PiecesMinifigsSets;

item_description	qty
black title	.11
black title
black title	.11

select sum(Qty) from PiecesMinifigsSets;

Expr1000
.22

select item_description,sum(Qty) from PiecesMinifigsSets group by item_description;

item_description	Expr1001
black title	.22

drop table PiecesMinifigsSets;
create table PiecesMinifigsSets (item_description text,qty text);
insert into PiecesMinifigsSets values('black title',' 0.11');
insert into PiecesMinifigsSets values('black title',' ');
select * from PiecesMinifigsSets;

item_description	qty
black title	0.11
black title

select sum(Qty) from PiecesMinifigsSets;
err.source:DAO.Database
err.desc:Data type mismatch in criteria expression.

I have a test on these above sql.

You can also run them one by one by yourself.

I have found that

even column qty contains null, it is ok to run sum function.

Only when column qty is Text data type, error Data type mismatch in criteria expression occour.

So check your fields

[Pieces/Minifigs/Sets].Qty) ([Pieces/Minifigs/Sets].Total)

Are they all number datatype?

Answer 2

[Pieces/Minifigs/Sets].Qty) - Is number datatypehttps://learn-attachment.microsoft.com/api/attachments/059d6f38-471d-4396-b992-05f1e0a5cc0a?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/542d2d14-360f-4477-9134-53da89b75b2d?platform=QnA" rel="ugc nofollow">

Answer 3

if possible share your database .accdb file here. https://wetransfer.com/upload

Can you try to modify your table name from Pieces/Minifigs/Sets to Pieces_Minifigs_Sets?

Answer 4

Pieces/Minifigs/Sets (table name from your sql)?

Cumulative Data (table name from your screenshot)?

How abou show me the data stucture of Pieces/Minifigs/Sets rather than Cumulative Data?

Answer 5

Pieces/Minifigs/Sets, is not a name of a column its different names that are in it. Those go in the "Item Type" field which is in the list of fields as a short text.