Query off Query on Val(field)

[StarvinMarvin]

I recently saved a 2007 database to 2003 for work in which everything worked OK in the 2003 version inside Access 2007. It appeared to work ok for a while until recently when I started having problems with 2 stored queries. Query 1 pulls employee data from a linked Oracle table and specifically converts the field STAR from text to a number, as I have no need for leading zeros in the results. Query 2 is used by a search form to produce a single employee's info based on STAR. Problem is when I attempt to search for employee info based on values I type into the form I get an ODBC error function sequence error #0 and a data type mismatch in criteria expression. This wasn't a problem before but for some reason now it is.

My thinking is that the first query to run is Query 1 which pulls ALL and converts STAR to number, then Query 2 selects from those results.

For some reason it looks like something's getting passed on to the Oracle table incorrectly.

I can't ask the operator to enter leading zeros to accomodate this problem and it doesn't make sense that I'd have to write code to check field length and add zeros before passing the values on to search by.

If this doesn't make sense I can' post the SQL.

 

[StarvinMarvin]

ok, this problem has to be the fault of the admins changing something on the server end or MS has pushed out an update in the last 24 hours that's screwing this up.

On a working distributed app that worked fine yesterday, the main form now fails to load with:

The expression is typed incorrectly, or it's too complex to be evaluated. (which is BS because it worked yesterday and today it doesn't without anyone touching the coding).

This has to do with a query that hits linked tables, because now the query won't run without this same error.

This is a MAJOR problem in an app that was put into production!!!

 

[StarvinMarvin]

The update that appears to have created this problem is KB976382 whic updated a visual basic dll file. This has caused a query on a linked ODBC Oracle table to stop functioning with the above listed error.

If anyone else is using queries on linked Oracle tables please let me know if you're having a similar problem.