Is it possible to have an Access query that runs fine in Access, but one of the criteria is too complicated for MS Query? (and the criterion isn’t all that complex!)
I am trying to pull some data into an Excel spreadsheet using MSQuery in order to produce a chart. I keep getting an ODBC-Call Failed error. I can add the Access query, but when I try to add any of the fields to the query grid, I get the error. None of my other 3 queries or tables I’m using with MSQuery is causing errors. When I remove the criterion, I can import data from the Access query, but of course it is not the data I want. The query criterion that seems to be having the problems is this:
Between ((Year(Now())-2000)*12)+Month(Now())-12 And ((Year(Now())-2000)*12)+Month(Now())-1
Allow me to intrepret. The data I’m reporting from is already aggregated by month, with each month designated by a month number (1 = January, 2000). The logic above returns a rolling list of the previous 12 months using this month number. It works in Access, but when I pull said Access query into MSQuery, it fails. If I remove the above criterion from the Access query and run it from MSQuery, it runs, but the data is wrong. What’s up with that?
