Data type mismatch in criteria expression (1 Viewer)

joce · Nov 18, 2011

I am creating a simple query in a farm audit database.

In criteria of the customers field, i have entered "Farmer One" as this is the customers data which I want to retrieve and in the Totals section I have changed the setting to Where instead of group by, but I am still getting "Data type mismatch in criteria expression" popping up.

Can anyone help as to why this is???

Any help would be great

gemma-the-husky · Nov 18, 2011

is the field TEXT or NUMERIC - ie - do you mean the number 1, rather than the text description "Farmer One"

Uncle Gizmo · Nov 18, 2011

>>>In criteria of the customers field, i have entered "Farmer One"<<<

When you run as a simple select query what results do you get?

vbaInet · Nov 18, 2011

Suspect lookup field. Remove them -->

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

gemma-the-husky · Nov 18, 2011

vbaInet said:
Suspect lookup field. Remove them -->

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

good thinking - clear candidate

Hey Lucy · Jan 21, 2013

vbaInet--

Read "The Evils of Lookup Fields in Tables". When users want a drop-down list to choose from (which may include hundreds of choices) I've only been taught to use Lookups. What is a better way? I'm getting a Data Mismatch when I try to write an expression on a form field and I do suspect the Lookup has something to do with it. Thanks!

Hey Lucy · Feb 20, 2013

Same problem here...but client has to have lookup field. However, it is the field with the lookup that I'm posting the criteria in. This is a real estate properties database and the Offer Status field is a lookup from the choices I created in the Offer Status table. When I try to run a query based on the criteria in the Offer Status field (criteria = Accepted), I get the data mismatch error. I am basing this query on two different tables. Any help is appreciated.

AlaQ · Sep 25, 2013

I know this post is an old one but I guess this comment might be able to help others. Sometimes you might get this error if you are creating a table with an AutoNumber as the primary key, without noticing it you create a new record that have null values in all other fields except for the AutoNumber. Then when you run any criteria on any other field you will be specifying a criteria for a record that has null values, let's say on the firstname. In this case you will get this error eventually. So delete the last record and you will be fine to go.

Data type mismatch in criteria expression (1 Viewer)

joce

Registered User.

gemma-the-husky

Super Moderator

Uncle Gizmo

Nifty Access Guy

vbaInet

AWF VIP

gemma-the-husky

Super Moderator

Hey Lucy

Registered User.

Hey Lucy

Registered User.

AlaQ

New member

Similar threads

Users who are viewing this thread