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

Why are you opening a recordset? How are you using it to export to Excel? — June7, Dec 27 '18 at 08:56
I am learning from a tutorial online and figuring out on my own. I am not sure why... Here is the code: — Guilherme Brum, Dec 27 '18 at 09:03
Dim i As Integer i=5 Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) with xlsheet Do While Not rs1.EOF .Range("A" & i).Value = Nz(rs1![Product Code], "") .Range("B" & i) ..... C, D, E, etc.. i = i + 1 rs1.MoveNext — Guilherme Brum, Dec 27 '18 at 09:12

June7 · Accepted Answer · 2018-12-27T09:29:01.137

0

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

edited Dec 27 '18 at 09:29

answered Dec 27 '18 at 08:40

June7

19,874
8
24
34

The problem I have is that one column is based on today's date, the other column is based on yesterday's date and so forth. Now, since I already have a query working and generating a data I need, is it possible to export it to excel with another method? – Guilherme Brum Dec 27 '18 at 08:50
The columns pulled into the query are not the issue. It is the filter criteria. You use the same filter criteria in all the SELECT statements. See options in my edited answer. – June7 Dec 27 '18 at 09:27
I used the temp table option with success. Maybe not the most elegant solution, but very practical and did the job. Thank you so much. – Guilherme Brum Dec 27 '18 at 10:16
Is this a multi-user database? If so, the db must be split and temp table located in frontend. Otherwise, multiple simultaneous users will conflict with each other writing records to shared table located in backend. – June7 Dec 27 '18 at 10:24

MS Access export Union Query to Excel, VBA problems

1 Answers1