Data type incompatible in the criteria expression when using a query and a table with a drop box

[lbcarvalho]

Hi,

I have created a table called PAYMENT TYPE, with fields like CREDIT CARD, CASH, CHECK, etc. I have used this first table to create a drop box in a second table. Now I want to build a query to show, say, every record with the field CASH, but every time I try this Access gives me the error message: Data type incompatible in the criteria expression.

What am I doing wrong?

Is it not possible to use a query to get only the field CASH, for instance?

Thanks in advance!

lbcarvalho

[Micron]

1st, all those options are attributes of entity "payment type" thus they ought to be records in tblPmtType, not fields in tblPayments. Improper normalization will continue to thwart you as it seems to be the case here. The litmus test is "If I need another payment type must I add another field?". If the answer is yes, the design is wrong.
The error message also suggests that the field is a lookup field in your table, which is another issue if true. While you see CASH, the actual relevant value is a number that corresponds to the position in a hidden table, like 1. This would explain your issue, I think. You can a) pass the correct number b) use the .Value property in the field (as in NameOfField.Value or c) do away with the lookup field.