Solution for Filter formula in Excel or Dynamic query in Access

Gilberto Sanches
Gilberto Sanches used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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.

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.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
You have it right Ryan.
I have a Excel Power Query connected with the Query in Access.
So based on the results of the query in Access, I want it automatically reflected in Excel.

How can I put the dynamic data in a static table (automatically)?
Ryan ChongSoftware Team Lead

Commented:
How can I put the dynamic data in a static table (automatically)?

you can't really do it "automatically", as it must be triggered by some "action". In this case, the action is probably the Click event of button "List Operator's Production", but you need to take care of scenarios such as concurrent access, etc since you save the result into a static table (different users could have different selections).
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Ryan, I configured the query to make a table. According to this article https://support.office.com/en-us/article/create-a-make-table-query-96424f9e-82fd-411e-aca4-e21ad0a94f1b

When I now run the query, through the FORM parameters, it gives the following error "Calculated columns are not allowed in SELECT INFO statements". I have 2 calculated columns. If I remove these 2 calculated columns from the query, it still gives the same error.
Ryan ChongSoftware Team Lead

Commented:
Table query doesn't consider as "static table" in this case. The "static table" I'm referring to is a table with data that didn't taking any parameters. You can treat the "static table" as the normal table.

When I now run the query, through the FORM parameters, it gives the following error "Calculated columns are not allowed in SELECT INFO statements". I have 2 calculated columns. If I remove these 2 calculated columns from the query, it still gives the same error.

I probably need to have your db (simplified) with sample data for debugging. can you upload it here?
ste5anSenior Developer

Commented:
Well, is you question about the entire process or just about Excel automation to refresh the data?

To refresh a certain PowerQuery connection use:

Option Compare Database
Option Explicit

Public Sub UpdatePowerQuery(ByVal CWorkbook As Excel.Workbook, ByVal CConnectionName As String)

  On Local Error GoTo LocalError

  Dim Connection As Excel.WorkbookConnection
  
  Dim RefreshRequired As Boolean
  
  For Each Connection In CWorkbook.Connections
    RefreshRequired = (Connection.Name = CConnectionName)
    If RefreshRequired Then
      Connection.Refresh
      Debug.Print Connection.Name & " refreshed."
      Exit For
    End If
  Next Connection

  Set Connection = Nothing
  Exit Sub
  
LocalError:
  Debug.Print "UpdatePowerQuery()"; Err.Number; Err.Description
  
End Sub

Open in new window

CConnectionName is the friendly connection name.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
@Ste5an, it's about having the data automatically prepared in Access, to then also automatically make a report of it in Excel.
The prepared data in Access is currently for all users & production period.
But the Report in Excel need to be per user & production period. That is where I want to use the filter, in Excel or in Access.
In Access I got something like a Filter working, but Excel Power Query can't use that filtered data.
So Ryan is helping with having the filtered data in a way that Excel Power Query can use it.

If the filtering can be done in Excel, it would be better. But then I'd have to create the complicated filter formula for 23 fields.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Ryan, here is a copy of the Access database https://drive.google.com/file/d/1Y3UrjZKBKJErtKxZmNEiK4GiJaYaO2bU/view?usp=sharing

Query - Operators Felling All Details Report 20200112 ; this is the prepared data without filters

Form - List Felling Operator's Production ;  this is for filtering
Make Table Query - Operators Felling Details Report 20200112 ; this is the result of the Filters
ste5anSenior Developer

Commented:
But the Report in Excel need to be per user & production period [..]
Thus you need a Excel template with an active connection to the local user database. Then you disconnect the Excel after the data is pushed into PowerQuery.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Thus you need a Excel template with an active connection to the local user database
,
exactly Ste5an.

I'll create the Excel template based on the way we'll get the data presented.
Ryan ChongSoftware Team Lead

Commented:
@Gilberto Sanches

I saw your query : Operators Felling Details Report 20200112 is actually trying to create a table called as : Operators Felling Details 20200113

...

INTO [Operators Felling Details 20200113]

FROM Users ...

so, are you trying to make table: [Operators Felling Details 20200113] as "static table" and the one will be shown in Excel PowerQuery as well?
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Yes Ryan, am using (Make Table) "Operators Felling Details 20200113" as static table.
And that static table is then being used in Excel Power Query.
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Maybe the way I am approaching the result is too complicated.
If there are other simple ways of achieving the same results,
  1. Have a automatically generated list of data, from 3 tables, with all ProductionPeriod & Usernames.
  2. Have that list be filtered based on ProductionPeriod & Username.
I am open to those solutions.
Software Team Lead
Commented:
am using (Make Table) "Operators Felling Details 20200113" as static table.
And that static table is then being used in Excel Power Query.

but query "Operators Felling Details 20200113" is having the "insert into" clause within, it can't really be used to display data.

and you're comparing the form's value in the query (Where clause), so it will cause an error if you run not from the form.

To resolve the issue:

1. Amend the query: [Operators Felling Details Report 20200112] to:

SELECT
'' & [ProductionPeriod F - Daily Sheet 20200110].ProductionPeriod_F as ProductionPeriod_F,
Users.Username,
'' & [ProductionPeriod F - Daily Sheet 20200110].Boomcode_DS_F as Boomcode_DS_F,
'' & [ProductionPeriod F - Daily Sheet 20200110].Fragments_DS_F as Fragments_DS_F,
[ProductionPeriod F - Daily Sheet 20200110].Date_DS_F,
[Production Period F_S LR 20191218].Username_M,
[Production Period F_S LR 20191218].nr_Fr_S,
[Production Period F_S LR 20191218].HoutSoort,
[Production Period F_S LR 20191218].PVD1,
[Production Period F_S LR 20191218].PVD2,
[Production Period F_S LR 20191218].PTD1,
[Production Period F_S LR 20191218].PTD2,
[Production Period F_S LR 20191218].Plengte,
[Production Period F_S LR 20191218].VD1,
[Production Period F_S LR 20191218].VD2,
[Production Period F_S LR 20191218].TD1,
[Production Period F_S LR 20191218].TD2,
[Production Period F_S LR 20191218].Lngte,
[Production Period F_S LR 20191218].SBB_Label,
[Production Period F_S LR 20191218].datum_M,
[Production Period F_S LR 20191218].Grade
FROM Users INNER JOIN
(
	[Production Period F_S LR 20191218] INNER JOIN [ProductionPeriod F - Daily Sheet 20200110] ON [Production Period F_S LR 20191218].BoomCode = [ProductionPeriod F - Daily Sheet 20200110].Boomcode_DS_F
) ON Users.ID = [ProductionPeriod F - Daily Sheet 20200110].[Operator name_DS_F]
WHERE ((([ProductionPeriod F - Daily Sheet 20200110].ProductionPeriod_F)=[Forms]![List Felling Operator's Production]![frm_fellers_prodperiod]) AND (([ProductionPeriod F - Daily Sheet 20200110].[Operator name_DS_F])=[Forms]![List Felling Operator's Production]![frm_fellers_name]));

Open in new window


A note that by using the expression of '' & for some of the fields, it resolved the error "Calculated columns are not allowed in SELECT INFO statements"

2. create the "static table" using SQL below:

SELECT
'' & [ProductionPeriod F - Daily Sheet 20200110].ProductionPeriod_F as ProductionPeriod_F,
Users.Username,
'' & [ProductionPeriod F - Daily Sheet 20200110].Boomcode_DS_F as Boomcode_DS_F,
'' & [ProductionPeriod F - Daily Sheet 20200110].Fragments_DS_F as Fragments_DS_F,
[ProductionPeriod F - Daily Sheet 20200110].Date_DS_F,
[Production Period F_S LR 20191218].Username_M,
[Production Period F_S LR 20191218].nr_Fr_S,
[Production Period F_S LR 20191218].HoutSoort,
[Production Period F_S LR 20191218].PVD1,
[Production Period F_S LR 20191218].PVD2,
[Production Period F_S LR 20191218].PTD1,
[Production Period F_S LR 20191218].PTD2,
[Production Period F_S LR 20191218].Plengte,
[Production Period F_S LR 20191218].VD1,
[Production Period F_S LR 20191218].VD2,
[Production Period F_S LR 20191218].TD1,
[Production Period F_S LR 20191218].TD2,
[Production Period F_S LR 20191218].Lngte,
[Production Period F_S LR 20191218].SBB_Label,
[Production Period F_S LR 20191218].datum_M,
[Production Period F_S LR 20191218].Grade
into tbl_tmp_ExcelPowerQuery
FROM Users INNER JOIN
(
	[Production Period F_S LR 20191218] INNER JOIN [ProductionPeriod F - Daily Sheet 20200110] ON [Production Period F_S LR 20191218].BoomCode = [ProductionPeriod F - Daily Sheet 20200110].Boomcode_DS_F
) ON Users.ID = [ProductionPeriod F - Daily Sheet 20200110].[Operator name_DS_F]
Where 1 = 2

Open in new window


3. Open your form : "List Felling Operator's Production" in Design Mode

change the button: "List operator's production" 's OnClick event from Macro Builder to Code Builder, and insert the following codes:

Private Sub List_operator_s_production___Command10_Click()
    Dim SQL As String
    'Drop tmp table
    Call DeleteTable("tbl_tmp_ExcelPowerQuery")
    
    'Reinsert tmp data
    DoCmd.SetWarnings False
    SQL = "Select * into tbl_tmp_ExcelPowerQuery from [Operators Felling Details Report 20200112]"
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    
    'Open result
    DoCmd.OpenQuery "Operators Felling Details Report 20200112", acViewNormal, acEdit
End Sub

Open in new window


on a separate note, create a public function: DeleteTable

Public Function DeleteTable(tableName As String) As Boolean
    On Error GoTo EH
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.DeleteObject acTable, tableName
        DeleteTables = True
    End If
    DeleteTable = False
    Exit Function
EH:
    DeleteTable = False
End Function

Open in new window


4. so now if you executing your form "List Felling Operator's Production" with filters, you will see the data in "static table": tbl_tmp_ExcelPowerQuery now is same as the data being displayed on form.

Untitled.jpg
5. test your Excel PowerQuery , like to create test.dqy

XLODBC
1
DBQ=C:\Users\xxxxx\Desktop\sample db_b.accdb;DefaultDir=C:\Users\xxxxx\Desktop;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;
SELECT *  FROM tbl_tmp_ExcelPowerQuery


ID

Open in new window


double click test.dqy to see your result.

Untitled.jpgsample-db_b.accdb
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Ryan! Your the man!
Where I gave up hope, you brought hope with your solution.
It works like a charm, eventhough I don't understand all the codes yet.

test.dqy works and even a manual made Power Query connection.

When I want to create a new list through the Form filter I get the error that it can't find DeleteTable.
I created the public function: DeleteTable in the same "Click Event Code Builder" of the button "List operator's production".
Public Function: DeleteTableWhere should I create the public function?
Ryan ChongSoftware Team Lead

Commented:
I created the public function: DeleteTable in the same "Click Event Code Builder" of the button "List operator's production".

you should create the public function in a new Module, not the same place with "Click Event Code Builder" of the button "List operator's production".

Untitled.jpg
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Okay, can you show me the steps or a reference online to creating that public function? & how do I delete the previous one?
 I did create the public function as a new module by the way,
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
to create the Public function I went to:
Ribbin menu -> Database -> Visual Basic -> Deleted the previous module without exporting it. Now the code is gone, but the name still exists.
I created a new module, pasted the code.
When I close Access, Access asks if I want to save changes to Module1. If yes, it asks for a name. That is how that module received the name previously.
Access question to save Module1
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
I created the Public Function as you suggested in a new module. I receive the same error
"Compile Error : Expected variable or procedure, not module"
Compilation error when running Form Filter
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
I think that this is the step where I went wrong
3. Open your form : "List Felling Operator's Production" in Design Mode

change the button: "List operator's production" 's OnClick event from Macro Builder to Code Builder, and insert the following codes:

When I open the button properties in Design view, s OnClick event shows me the following options: Embedded Macro or Event Procedure.
Button 's OnClick event options
When I press ... on the Embedded Macro I get the following window
Macro Event builder
Earlier I had choosen for "Event Procedure" and then on ... of the "Event Procedure" and then got the window "Visual Basic window."
There I inserted the code. After that created a new module for the public function & gave it the name "DeleteTable"
The 2 codes for the button "List operator's production"
When I click on the "List Operator's Production" button, I then get the following error
Compiler error
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Ahaha, you won't believe what the solution was. I deleted the line

Dim SQL As String
    'Drop tmp table
    Call DeleteTable("tbl_tmp_ExcelPowerQuery")

Open in new window


So the only code on the button is

Private Sub List_operator_s_production___Command10_Click()

    'Reinsert tmp data
    DoCmd.SetWarnings False
    SQL = "Select * into tbl_tmp_ExcelPowerQuery from [Operators Felling Details Report 20200112]"
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    
    'Open result
    DoCmd.OpenQuery "Operators Felling Details Report 20200112", acViewNormal, acEdit

End Sub

Open in new window


It now works awesome. Thanks again Ryan
Ryan ChongSoftware Team Lead

Commented:
Ok, glad and it seems that you have resolved the issue while I was away

= )
Gilberto SanchesFreelance ICT Consultant

Author

Commented:
Yeah, cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial