MS Access export Union Query to Excel, VBA problems

Question

I am not a programmer but I am building a Access database for work. The database is working ok now, but I am struggling to export one important Query to excel. Thank you for the help and sorry for bad practices and probably many syntax errors.

From the Union Query below named "Rework Flow Until Today", I created one query to group by Product Code named "Rework Flow Until Today Query", then another query to add columns to make some calculation which I wish to export to Excel.

The Query I need to export is named "Export Query" . It looks like this:

(Product / Start Rework Stock / Used Rework / New Rework / Total rework )

These are calculated fields from the data obtained in Union Query and then Grouped query.

Here is the union query code:

SELECT DISTINCTROW 
Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Rework Used] AS [Rework Used TODAY lb 1], Batch.[Support row] AS [Rework Used YESTERDAY lb 1], 
Batch.[Support row] AS [Rework Used TODAY lb 2], 
Batch.[Support row] AS [Rework Used YESTERDAY lb 2], 
Batch.[Support row] AS [New Rework TODAY lb], 
Batch.[Support row] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.[Rework Product] = Products.[Product ID]
WHERE (((Batch.[Production Date])=[Forms]![01 Production Batch Form]![Today Date]))

UNION ALL

SELECT DISTINCTROW 
Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Support row] AS [Rework Used TODAY lb 1], 
Batch.[Rework Used] AS [Rework Used YESTERDAY lb 1], 
Batch.[Support row] AS [Rework Used TODAY lb 2], 
Batch.[Support row] AS [Rework Used YESTERDAY lb 2], 
Batch.[Support row] AS [New Rework TODAY lb], 
Batch.[Support row] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.[Rework Product] = Products.[Product ID]
WHERE (((Batch.[Production Date])<[Forms]![01 Production Batch Form]![Today Date]))

UNION ALL

SELECT DISTINCTROW 
Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Support column] AS [Rework Used TODAY lb 1], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 1], 
Batch.[Rework Used 2] AS [Rework Used TODAY lb 2], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 2], 
Batch.[Support column] AS [New Rework TODAY lb], 
Batch.[Support column] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.[Rework Product 2] = Products.[Product ID]
WHERE (((Batch.[Production Date])=[Forms]![01 Production Batch Form]![Today Date]))

UNION ALL

SELECT DISTINCTROW 
Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Support column] AS [Rework Used TODAY lb 1], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 1], 
Batch.[Support column] AS [Rework Used TODAY lb 2], 
Batch.[Rework Used 2] AS [Rework Used YESTERDAY lb 2], 
Batch.[Support column] AS [New Rework TODAY lb], 
Batch.[Support column] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.[Rework Product 2] = Products.[Product ID]
WHERE (((Batch.[Production Date])=[Forms]![01 Production Batch Form]![Today Date]))

UNION ALL

SELECT DISTINCTROW Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Support column] AS [Rework Used TODAY lb 1], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 1], 
Batch.[Rework Used 2] AS [Rework Used TODAY lb 2], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 2], 
Batch.[New Rework] AS [New Rework TODAY lb], 
Batch.[Support column] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.Product = Products.[Product ID]
WHERE (((Batch.[Production Date])=[Forms]![01 Production Batch Form]![Today Date]))

UNION ALL 

SELECT DISTINCTROW 
Batch.[Production Date], Products.[Rework Family] AS [Product Code], 
Batch.[Support column] AS [Rework Used TODAY lb 1], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 1], 
Batch.[Rework Used 2] AS [Rework Used TODAY lb 2], 
Batch.[Support column] AS [Rework Used YESTERDAY lb 2], 
Batch.[Support column] AS [New Rework TODAY lb], 
Batch.[New Rework] AS [New Rework YESTERDAY lb]
FROM Batch INNER JOIN Products ON Batch.Product = Products.[Product ID]
WHERE (((Batch.[Production Date])<[Forms]![01 Production Batch Form]![Today Date]));`

When I use the "Union Query" in my code to export to excel, I add the #" & and & "# before and after the dates, but I get error number 3129. When I use the "Export Query" in my code I get error 3061 Too few parameters, Expected 1.

here is the Export Query code:

SELECT DISTINCTROW 
[Rework Flow until Today].[Product Code], 
Sum([Rework Flow until Today].[Rework Used TODAY lb 1]) AS [SumOfRework Used TODAY lb 1], 
Sum([Rework Flow until Today].[Rework Used YESTERDAY lb 1]) AS [SumOfRework Used YESTERDAY lb 1], 
Sum([Rework Flow until Today].[Rework Used TODAY lb 2]) AS [SumOfRework Used TODAY lb 2], 
Sum([Rework Flow until Today].[Rework Used YESTERDAY lb 2]) AS [SumOfRework Used YESTERDAY lb 2], 
Sum([Rework Flow until Today].[New Rework TODAY lb]) AS [SumOfNew Rework TODAY lb], 
Sum([Rework Flow until Today].[New Rework YESTERDAY lb]) AS [SumOfNew Rework YESTERDAY lb]
FROM [Rework Flow until Today]
GROUP BY [Rework Flow until Today].[Product Code];

Below is my VBA code

Dim SQL As String
Dim rs1 As DAO.Recordset
SQL = " One of the queries here "
Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

thank you

Answer 1

VBA to open a recordset with a query that has dynamic parameters won't work nor if any query in the sequence has dynamic parameters. Don't put filter criteria in query, put it in code that opens recordset.

SQL = "SELECT * FROM queryname WHERE [Production Date]=#" & [Forms]![01 Production Batch Form]![Today Date] & "#"

Unfortunately, [Production Date] field is not available in the final query. Alternative options:
1. TransferSpreadsheet
2. write records to a 'temp' table - table is permanent and records are temporary
3. export a report object that uses the final query as its RecordSource