We help IT Professionals succeed at work.
Get Started

Solution for Filter formula in Excel or Dynamic query in Access

148 Views
Last Modified: 2020-01-15
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?
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 23 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE