We have a Query in Access that brings information from multiple tables & Queries together.
But this merge now needs to dynamically narrowed down based on 2 fields/criteria's. The last is then needed in Excel to create reports (pivot tables stuff).
Since Excel doesn't have the easy filter formula like Google Docs. I thought of creating the 2 dynamic filters in Access based on the following YouTube video.
They called it a "Form-Based Parameter Query - Dynamic Queries".
It shows the results just like I want. https://www.youtube.com/watch?v=mnWidUABYdQ
But then I face the following challenge. The results from this "Form based Parameter Query" I automatically want in Excel through a Power Query refresh.
Without the "Form-Based Parameter Query - Dynamic Queries" I was able to bring the data from that query in Excel. But then without the 2 filters.
But since I made the "Form-Based Parameter Query - Dynamic Queries" in Access, Excel Power Query gives error" [Expression.Error] The key didn't match any rows in the table." when refreshing the query. When I want to recreate the Power Query in Excel, it now doesn't show the Query from Access that is attached to the "Form based Parameter Query".
What solutions are there in such situation?
not too sure if I understand fully about your requirement, is that mean once you filtered the records in Access Form, you want the Excel Power Query to be automatically filtered as well?
if yes, in your Access, try to put your filtered data into a static table. And then in your Excel Power Query just refer to that static table instead.