Query Condition

[Hawkeyesv]

I tried to make a criteria in access to only show entries that are not “US”. When I used “” or <> I get the following error message: Data type mismatch in criteria expression. When I looked at the table, the field is short text so I’m not sure what is going on. Thanks in advance for your help!

[mike60smart]

Hi

Use this: Not In("US")

[ranman256]

dont use different dbl-quotes. use only "" (left of the ENTER key)
then you can use <> "US"

that slant dbl-quote is not even on the keyboard.

[Welshgasman]

You could be using table lookup fields, so it is not "US" at all, but a numeric?

[Hawkeyesv]

Hi

Use this: Not In("US")

Unfortunately that did not work

[Hawkeyesv]

What you provided was what I tried- I made a typo in my post

[Hawkeyesv]

I originally thought that, but I have a different query that made a criteria to be "US" and it worked. So it is recognizing the field as text

[Welshgasman]

Might be time to show the query sql ?

[mike60smart]

Can you upload the database?

[Hawkeyesv]

Might be time to show the query sql ?

#SELECT DISTINCT [Vsl Act Team Members DT].Field15, [Vsl Act Team Members DT].Field26, [ Vsl Act Team Members DT].Field24, [Vsl Act Team Members DT].Field32, [Vsl Act Team Members DT].Field43, [Vsl Act Team Members DT].Field45, [Vsl Act Team Members DT].Field17, [Vsl Act Team Members DT].Field2, DateValue([Field4]) AS Expr1

FROM [Vsl Act Team Members DT]

WHERE ((([Vsl Act Team Members DT].Field33) Not In ("US")))

ORDER BY DateValue([Field4]) DESC;

[Micron]

I originally thought that, but I have a different query that made a criteria to be "US" and it worked. So it is recognizing the field as text

That query is for the same field in the same table? If not, I'm inclined to agree with the numeric idea. Try a new select query and for US field, enter a number (e.g. 2) and see if a) it allows that, and b) if it returns something.

[Hawkeyesv]

That query is for the same field in the same table? If not, I'm inclined to agree with the numeric idea. Try a new select query and for US field, enter a number (e.g. 2) and see if a) it allows that, and b) if it returns something.

I changed the format from long to short text, and it appears to be functioning now. Thank you!

[Micron]

Well I appreciate the thanks but I'm confused. I don't see how what I suggested helped given what you've just reported. Plus you originally said it was short text, now you're saying you changed it from long to short

When I looked at the table, the field is short text so I’m not sure what is going on.

When using text criteria on a text field, it should not matter if it is short or long anyway, so there's that too.

[Hawkeyesv]

So it turns out it’s actually not working. The error message does not appear when I type in a number for the criteria. But in the table that feeds like the query the data type is clearly text. Why is this?

[Minty]

I suspect you have used lookup fields in your table design.

They can be very confusing, and are generally discouraged. http://access.mvps.org/Access/lookupfields.htm