时间戳

首页 > 解决方案 > MS Access导出联合查询到Excel，VBA问题

ms-access - MS Access导出联合查询到Excel，VBA问题

问题描述

我不是程序员，但我正在为工作构建一个 Access 数据库。数据库现在工作正常，但我正在努力将一个重要的查询导出到 excel。感谢您的帮助，并对不良做法和可能的许多语法错误表示歉意。

从下面名为“直到今天的返工流程”的联合查询中，我创建了一个查询来按名为“直到今天的返工流程查询”的产品代码分组，然后创建另一个查询来添加列以进行一些我希望导出到 Excel 的计算。

我需要导出的查询名为 "Export Query" 。它看起来像这样：

（产品/开始返工库存/二手返工/新返工/总返工）

这些是从联合查询中获得的数据，然后是分组查询中计算出来的字段。

这是联合查询代码：

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

当我在代码中使用“联合查询”导出到 excel 时，我在日期前后添加了 #" & 和 & "#，但出现错误号 3129。当我在代码中使用“导出查询”时我收到错误 3061 参数太少，应为 1。

这是导出查询代码：

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

下面是我的 VBA 代码

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

谢谢你

标签： ms-accessvbaexport-to-excelms-access-2016union-all

解决方案

如果序列中的任何查询具有动态参数，VBA 将无法使用具有动态参数的查询打开记录集。不要将过滤条件放在查询中，将其放在打开记录集的代码中。

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

不幸的是，[生产日期] 字段在最终查询中不可用。替代选项：
1. TransferSpreadsheet
2. 将记录写入“临时”表 - 表是永久的，记录是临时
的 3. 导出使用最终查询作为其 RecordSource 的报表对象

推荐阅读