Solution for Filter formula in Excel or Dynamic query in Access

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]));

Select all 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

Select all 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

Select all 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

Select all 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.


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

Select all Open in new window

double click test.dqy to see your result.

sample-db_b.accdb