如何调用VBA函数在SQL转换查询中指定列?

是的,有可能。
但是,我认为不可能 WHERE IN (...) .

下面是一个普通的例子 WHERE 查询:

Public Function Test() As String
    Test = "Smith"
End Function

SELECT * FROM Users WHERE Name = Test();

但我认为 不

SELECT * FROM Users WHERE Name In (Test());

(至少我不知道怎么做)

如果在PIVOT子句中排除了IN列表,则转换查询将自动为创建列 每个轴值 从SELECT语句生成。可以通过使用“硬编码”(即文字)值指定IN表达式来过滤最后的列。这当然是从其他答案知道的。

通过限制SELECT查询中的数据以开始。。。 转换需要过滤它。这样,就不仅限于预定义的文字值,而且联接、子查询和/或VBA函数的组合也可以对数据进行预过滤,从而有效地选择在转换表中显示哪些列。请注意,HAVING子句在转换查询中是不允许的,但是它可以在转换选择的另一个查询中使用,因此在转换之前如何准备数据实际上没有限制。

TRANSFORM Count(Services.ID) AS [Schedules]
SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account
WHERE ( Services.Code = "IS" )
GROUP BY Agreement.City ORDER BY Agreement.City
PIVOT Month([ServiceDate]) In (1,4,12)

在WHERE子句中使用IN运算符:

TRANSFORM Count(Services.ID) AS [Schedules]
SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account
WHERE ( (Month([ServiceDate]) In (1,4,12)) AND Services.Code = "IS" )
GROUP BY Agreement.City ORDER BY Agreement.City
PIVOT Month([ServiceDate])

但与PIVOT…IN子句不同,列表也可以是另一个查询:

WHERE ((Month([ServiceDate]) In (SELECT Values FROM PivotValues)) AND Services.Code = "IS" )

TRANSFORM Count(Services.ID) AS [Schedules]
SELECT Agreement.City FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account
WHERE ( ReportMonth([ServiceDate]) AND Services.Code = "IS" )
GROUP BY Agreement.City ORDER BY Agreement.City
PIVOT Month([ServiceDate])

使用标准模块中定义的VBA函数:

Public Function ReportMonth(dt As Date) As Boolean
  Select Case Month(dt)
    Case 1, 4, 12: ReportMonth= True
    Case Else:     ReportMonth= False
  End Select
End Function

(iDevlop已经在一篇评论中提出了这个解决方案,但我不认为它被理解,需要好的例子。)

变换中的保证列包含

Public Function GenerateTransform(valueArray As Variant) As String
  Dim sIN As String
  Dim i As Integer, delimit As Boolean

  If (VarType(valueArray) And vbArray) = vbArray Then
    For i = LBound(valueArray) To UBound(valueArray)
      sIN = sIN & IIf(delimit, ",", "") & valueArray(i)
      delimit = True
    Next i
    If Len(sIN) > 0 Then sIN = "IN (" & sIN & ")"
  End If

  GenerateTransform = "TRANSFORM ... SELECT ... PIVOT ... " & sIN

End Function

Public Sub TestGenerateTransform()
  Dim values(0 To 2) As Integer
  values(0) = 1
  values(1) = 4
  values(2) = 12

  Debug.Print GenerateTransform(values)
  Debug.Print GenerateTransform(vbEmpty) 'No column list 
End Sub

第一个查询应用选择条件并进行初始分组。如果与我的另一个答案相比,这与原始的转换查询本质上是相同的——只是没有转换和透视。保存并命名此查询 :

SELECT Agreement.City, Month([ServiceDate]) AS [Month], Count(Services.ID) AS Schedules
FROM Agreement INNER JOIN Services ON Agreement.Account = Services.Account
WHERE (Services.Code = "IS")
GROUP BY Agreement.City, Month([ServiceDate])
ORDER BY Agreement.City

接下来,创建一个查询,该查询对所有所需的行值进行分组。在本例中,我选择只包含初始选择条件中的相同值。^^通过基于未筛选的表或其他查询,这组值也可以与以前的选择条件分离。保存并命名此查询 [2行标题] :

SELECT RowSource.City AS City
FROM [1 Initial Aggregate] AS RowSource
GROUP BY RowSource.City
ORDER BY RowSource.City

创建行标题和包含列标题的辅助表[PivotValues]的交叉联接。交叉联接从两个表的每个组合中创建行——在Access SQL中,它是通过排除所有联接关键字来实现的。保存并命名此查询 [3交叉连接] :

SELECT [2 Row Headings].City AS City, PivotValues.Values AS Months
FROM [2 Row Headings], PivotValues
ORDER BY [2 Row Headings].City, PivotValues.Values;

最后,转换:通过使用左连接,这将包括交叉连接查询中存在的所有列和行。 值为Null。 尽管我们已经对初始查询进行了分组,但是转换仍然要求我们重新分组——也许有点多余,但对最终交叉表结果的控制并不是什么大不了的事。

TRANSFORM Sum([1 Initial Aggregate].Schedules) AS SumOfSchedules
SELECT [3 Cross Join].City AS City
FROM [3 Cross Join] LEFT JOIN [1 Initial Aggregate] ON ([3 Cross Join].Months = [1 Initial Aggregate].Month) AND ([3 Cross Join].City = [1 Initial Aggregate].City)
GROUP BY [3 Cross Join].City
PIVOT [3 Cross Join].Months

为什么不在表中添加这些标题并在xtab查询中联接该表呢?
这可能更容易在函数中维护硬编码。

正如您所说,您使用的是Access,那么(我突然想到)是的,在查询中使用VBA函数是可能的。