troubleshooting Question

Solution for Filter formula in Excel or Dynamic query in Access

Avatar of Gilberto Sanches
Gilberto SanchesFlag for Suriname asked on
Microsoft OfficeGoogleMicrosoft ExcelMicrosoft Access
23 Comments1 Solution151 ViewsLast Modified:
The Form with the 2 filters (left) & the resulting query (right)Hi experts,

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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros