Data mismatch error

[Chris S]

Hi There - I am trying to create a basic query that excludes certain records.

My table has a field that is status. This is a lookup value that I created
using the wizard and it is stored as a number field type. I am trying to
create a simple query where I exclude one of the status types. However, I
keep getting the "Data type mismatch criteria expression" error. I am not
sure how to go about fixing this since my field in my table is a look up?

 

[KARL DEWEY]

This is a lookup value that I created using the wizard and it is stored as
a number field type.
Your criteria must match the field datatype you are applying it to.

Is it a lookup field in the table or is it joined to a lookup table? If it
the latter then join the lookup table in your query and apply the criteria to
the text field.

If it is a lookup field in your table then you should change it.

 

[John W. Vinson]

Hi There - I am trying to create a basic query that excludes certain records.

My table has a field that is status. This is a lookup value that I created
using the wizard and it is stored as a number field type. I am trying to
create a simple query where I exclude one of the status types. However, I
keep getting the "Data type mismatch criteria expression" error. I am not
sure how to go about fixing this since my field in my table is a look up?

See http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup
Field misfeature.

Your problem is that the table APPEARS to contain a text status. Of course it
doesn't. It contains a (concealed from your view) numeric ID.

You'll need to either use

<> 31

as the criterion (if it's the status with ID 31 that you want to exclude), or
include the lookup table joined to your main table in the query and use a
criterion of

<> "Expired"

on the (text) status field, from the linked status table.

The latter is preferable since the numeric ID might change.