Excel not using Access query criteria

[thart21]

I have never come across this before and hope someone has. I have a simple select query in which I have 3 fields with criteria.

ProductID - Not Like *P
Qty - >0
NeedDt => 1/1/2011

The query runs perfectly in Access and eliminates any ProductID with a "P" on the end of it.


When I import the query into Excel, it disregards this criteria and all of the ProductID's with and without a "P" at the end come across. I have all of my criteria on the 1st line, tried moving the "P" criteria down one and Excel kept crashing on import.

Any suggestions as to how to fix this would be appreciated.

Thanks,

Toni

[TheShabz]

Try running the query in access then exporting the results to excel. Why write a query in Access then run it in Excel?

[thart21]

That does work, tried it, but, if I do that, can my user's who will be opening the Excel file on a daily basis be able to refresh the data?

The problem, I found out this morning, is that Excel does not like the wildcard "*", it wants "%"

[TheShabz]

If it's the same query every time, set it as a scheduled task to run the query and overwrite the previous excel file.

[thart21]

No can do. Multiple users will want to refresh it at any given time during the day. Question out of curiosity, why are you against importing data from Access into Excel, is it a performance issue? It seems to work for me quite well most of the time and, as I'm sure you know, more "non techy" type people seem to be comfortable using Excel than Access. Would like your insight on it for future reference as I'm always up for changing direction if it will make things work better. Thanks for the help!

[TheShabz]

It's just that I'm very much in favor of using software for its intended purpose. Perhaps I'm not understanding the actual process and what is exactly going on, but writing a query/filter/whatever in access then wanting it run in Excel defeats the purpose of having Access to begin with.

Maybe if you explain in better detail what the requirement actually is, I can understand where youre coming from better.

[thart21]

Here's what I have:
3 Oracle tables residing on 2 different servers linked in Access.
2 queries built off of these 3 tables, then my final query from those 2.
Users want 1) Excel worksheet with all of the data from the query and 2) 2 pivot tables from that data 3) The ability to open the spreadsheet at any time and refresh the data and the pivot tables. 4) They do not want to have to go into Access at all

From my brief experience with MS Query in Excel, I can't perform the joins and the IIF statements and calculations that I need, thus the reason I am trying to pull it from Access into Excel. My other glitch is I have Office 2007 and my users are a mix of 2003 and 2007. The 2003 users don't seem to be able to use my workbooks even after I follow the instructions to save it in compatibility mode as a .xls. Very frustrating and, I agree, I would love to have them use Access, that's what I know better and it would make life much easier. Thank you for sharing your insight with me.

[TheShabz]

So you are querying your real-time production database? I get nasty phonecalls from people when I do that.

Anyway, I don't really know how (or if it's even possible) to do what you want the way you want it. What I've always done for those who dont want to use Access is meet them in the middle. I would make a db for them with a form that automatically opens. I have 2 buttons on that form. First one does all the querying and the second exports to Excel. Yes, they have to open up Access (and you'll have to get the ODBC connection to your Oracle backend setup for them), but its a 2 click procedure and I've never had anyone raise a fuss. Generally, if it works, they'll go with it.

[thart21]

I could try that, but I have to make the pivot tables for them and they need to be refreshed with the data each time. I'll look into that. Thanks again for the tips.

[TheShabz]

Hell, if you really want to impress them, you can have Access take control of Excel and populate the sheet and create the pivot tables all while that Excel sheet is open. It looks pretty cool. Generally speaking, its much easier to have Access do Excel functions than Excel do Access.

[thart21]

Well since I'm a temp at a major company and would love to be hired permanently (if that means anything anymore) I definitely DO want to impress them! Thanks again.