如何在Access查询中动态评估字符串规则表达式生成布尔结果？

问题核心 #

需要用存储在TheRules表中的布尔规则字符串，批量校验Employee表的记录，但直接用Eval()无法识别查询字段上下文，自定义函数不知道如何传递字段值计算规则。

解决方案 #

1. 创建VBA自定义函数

打开Access的VBA编辑器（按Alt+F11），插入一个模块，添加以下函数：

Function EvaluateRule(ruleStr As String, empID As Long, firstName As String, lastName As String, wage As Double, tenure As Double) As Boolean
    ' 把规则里的字段占位符替换为当前员工的实际值
    Dim evaluatedStr As String
    evaluatedStr = Replace(ruleStr, "[ID]", CStr(empID))
    evaluatedStr = Replace(evaluatedStr, "[First Name]", Chr(34) & firstName & Chr(34))
    evaluatedStr = Replace(evaluatedStr, "[Last Name]", Chr(34) & lastName & Chr(34))
    evaluatedStr = Replace(evaluatedStr, "[Wage]", CStr(wage))
    evaluatedStr = Replace(evaluatedStr, "[Tenure]", CStr(tenure))
    
    ' 计算表达式并返回结果，出错时默认返回False
    On Error Resume Next
    EvaluateRule = Eval(evaluatedStr)
    If Err.Number <> 0 Then
        EvaluateRule = False
        Err.Clear
    End If
    On Error GoTo 0
End Function

2. 修改查询语句

调整查询，将员工的每个字段作为参数传给自定义函数，同时给规则ID加别名避免字段冲突：

SELECT 
    Temp.*, EvaluateRule([Temp.TryMe], [Temp.ID], [Temp.First Name], [Temp.Last Name], [Temp.Wage], [Temp.Tenure]) AS Checked
FROM 
    (SELECT 
         Employee.ID, Employee.[First Name], Employee.[Last Name], 
         Employee.Wage, Employee.Tenure, 
         TheseRules.ID AS RuleID, TheseRules.Check AS Tryme
     FROM 
         Employee, TheRules AS TheseRules) AS Temp;

关键说明 #

• 文本类型字段（如姓名）需要用双引号包裹，用Chr(34)生成双引号可避免字符串拼接错误。
• 错误处理能防止规则表达式语法错误导致整个查询崩溃，可根据需求修改出错时的返回值。
• 如果Employee表有更多字段，只需在函数中添加对应的替换逻辑，并在查询中传入该字段即可。

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