Error message - data type mismatch in criteria expression - in query

I have created several tables of information.  The main table has drop down fields that are linked to the other tables.  Everything works great until I want to run a query based on any of the fields in the main table that are drop down fields.  I get the error message "Data type mismatch in criteria expression".  This only happens if the field in the criteria field of the query is a drop down in the main table.

 

Any thoughts how I can fix this would be greatly appreciated.

Connie

 

Question Info


Last updated February 5, 2018 Views 1,473 Applies to:
Answer
Answer
While you see a text value in what you call a 'drop down' field, this is not the actual value in the field.  The real value is a number which references the numeric primary key, usually an autonumber, of the referenced table.  To apply a criterion based on the text value you must join the table to the referenced table in the query and return the text column (field) from the referenced table.  You can then apply a text criterion to that column.

I imagine you used the 'lookup field' wizard when adding these columns to the table in design view.  This feature should be avoided, however.   For reasons why see:

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

Seeing the value from the referenced table is unnecessary in a table's datasheet view as data should only be accessed via forms or reports, never in raw datasheet view.  Bear in mind that a form can be in datasheet view.  In a form you can build a combo box (or allow the control wizard to do so) which shows you the values from the referenced table in the same way as a 'lookup field' does, but without the undesirable consequences detailed at the above link.  In a report you can join the tables in a query and bind a control to the column in the referenced table.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.