Access/Excel Query link with Criteria

[Rich_A]

Hi Everyone, I've hit a bit of a wall here and hoping someone can help. I have an Access query with criteria established to limit the output to a more digestible size. When I link this query to Excel, though, it is disregarding the Criteria and bringing in the full query. I'm wondering if this is a setting in Excel that will allow me to only bring in the Query per the criteria established in Access, and maintain the link (as opposed to exporting it every time). Thanks to anyone who may be able to help - warmly appreciated.

 

[welshgasman]

How are you linking?
Are you linking, or just using Data/From Access

 

[Rich_A]

Hi Welshgasman, thank you for the reply. I was linking through this process:

Data -> Get Data -> From Database -> From Microsoft Access Database
Selected the Query (with criteria already applied in Access) in the Navigator dialog box
When the preview appears, and the subsequent data uploads, the full Query is uploaded rather than the limited version the criteria provided.

Thank you for any advice you can provide

 

[welshgasman]

OK, that is how I have just done it and only the relevant data was extracted.?

However if I went back into the query and changed the criteria and saved the query, that change was not reflected in Excel. It is more of a snapshot, not linked as you can from Access to Excel.?

So I would double check your criteria.?
What is the criteria BTW ?

 

[Rich_A]

that actually is very helpful because if you can do it on your end but i can't do it on this end - at least it can be done (which i wasn't sure of before). My working theory is it is a setting on my end.

The Criteria I am using is text based and eliminates years from our company "job numbers". For example, a 2019 job number is 19-0001-RA. I've set criteria to eliminate that year using: Not Like "19-*"

I eliminate additional years using And Not Like "18-*" etc. etc.

 

[welshgasman]

Well I tried a query that asked or a parameter and that produced an empty table.
Now I have tried a query that has criteria and produces 8 records in Access, but nothing comes across into Excel.

Not something I have used TBH, always the other way.? I'm on 2007 BTW.

 

[Rich_A]

i'm with you. thinking a gool old fashioned export may be in order. hate to have the extra step, but at least it will work