'Data type mismatch in criteria expression' - when filtering query

[RogerH]

A very complex query, involves four tables (about 2/3 of that data in the enterprise system that it is) and 4 very complex complex criteria. one of which is so complex I had to write a function for it)

THAT works.

But if the user right-clicks on the form (or me on the query) and tries to filter by Customer ID number or Name, "Data type mismatch in criteria expression" Its a number, being filtered by a number. it is NOT a type mismatch.

Now CustID IS one of the key fields linking the tables together. But I'm sure I've filtered by keys before in 20 years of coding MSA. (and name sure isn't)

I'm stuck here, :banghead: any ideas??

ALREADY TRIED:
1. hardcoding a form filter
2. adding the criteria to the query(with LIKE and/or various arrangements of quotes)
3. building a 2-step query
twice: filtering to the custID first, or second.

 

[plog]

My gut says this isn't a form issue, its a query issue. Open your very complex query in Datasheet View and then sort a column (use the ribbon or right click). I bet that query has the same issue your form has.

My guess is somewhere in all that complexity is an issue that's non-fatal to the query running--but when you try to sort or even filter--it breaks. If I am right you need go through it piece by piece to find the offender. Here's how to do it in general:

1. If built on other queries, start at the bottom. Open each sub-query in Datasheet view and try and sort. If it sorts, that query's not the issue, move to the next one.

2. Once you find the offending query, make a copy and yank out fields until you can sort it. The last one you yank is the offender.

 

[RogerH]

nevermind.

worked around it, I *had* written a function for filtering, so I added the custID filter to that function.

(still would love to know why that wasn't workin)

 

[JHB]

..
(still would love to know why that wasn't workin)

Difficult to say without hands on the database.

 

[Minty]

I've seen this behavior on complicated queries. Access will silently try and requery with a either an Order By or Where or Having clause depending on the queries involved.

Sometimes this is the straw that breaks the camels back. As plog said work backwards through them.

The error can vary depending on the complexity.