如何在Access VBA中获取已保存查询的字段名与查询条件

一、获取查询字段名 #

直接利用QueryDef的Fields集合遍历即可，无需解析SQL：

Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("目标查询名") ' 替换为你的查询名称

Dim fld As Field
For Each fld In qd.Fields
    Debug.Print fld.Name ' 输出ID、CarName等字段名
Next fld
Set qd = Nothing

二、获取并解析查询条件 #

不要手动拆分完整SQL字符串，Access的QueryDef提供了WhereClause属性，直接返回WHERE子句的内容，这是最可靠的方式。在此基础上可以进一步处理得到独立的条件表达式：

1. 核心函数：清理条件字符串并拆分独立条件

Function SplitWhereConditions(whereStr As String) As Collection
    Dim condCollection As New Collection
    Dim cleanedStr As String
    
    ' 去除首尾冗余括号
    cleanedStr = Trim(whereStr)
    If Left(cleanedStr, 1) = "(" And Right(cleanedStr, 1) = ")" Then
        cleanedStr = Mid(cleanedStr, 2, Len(cleanedStr) - 2)
    End If
    
    ' 按AND拆分条件（如需支持OR，可扩展逻辑）
    Dim condArray() As String
    condArray = Split(cleanedStr, " AND ")
    
    Dim i As Integer
    For i = LBound(condArray) To UBound(condArray)
        Dim singleCond As String
        singleCond = Trim(condArray(i))
        
        ' 去除单个条件的冗余括号
        If Left(singleCond, 1) = "(" And Right(singleCond, 1) = ")" Then
            singleCond = Mid(singleCond, 2, Len(singleCond) - 2)
        End If
        
        ' 移除表名前缀（如AllCars.），按需保留或删除此步骤
        singleCond = Replace(singleCond, CurrentDb.QueryDefs("目标查询名").Tables(0) & ".", "")
        
        condCollection.Add singleCond
    Next i
    
    Set SplitWhereConditions = condCollection
End Function

2. 调用示例

Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("目标查询名")

Dim conditions As Collection
Set conditions = SplitWhereConditions(qd.WhereClause)

' 遍历输出所有条件
Dim cond As Variant
For Each cond In conditions
    Debug.Print cond ' 输出：ID Between 15 And 25、MinPay<1000
Next cond

Set conditions = Nothing
Set qd = Nothing

三、注意事项 #

• 如果查询包含嵌套子查询、复杂括号嵌套或OR逻辑，需要扩展函数的解析逻辑（比如用正则表达式匹配完整的括号块）
• WhereClause属性会自动处理SQL中的转义字符（如原SQL中的<会还原为<），无需手动处理转义

内容的提问来源于stack exchange，提问作者meldaw