MS Access：如何简化查询中自定义函数getCoefC的调用语法？

现有情况 #

我有查询qryA，通过自定义VBA函数getCoefC从tblLinkCoefficients表中获取特定系数，用于计算字段。当前函数需要传入带表前缀的字段值，调用语句冗长，不利于后续多次调用不同系数。

现有getCoefC函数代码 #

Public Function getCoefC(strrecid As Integer, strchemid As Integer, strcoefid As Integer) As Double
'***************************************************************************
'Purpose: Search for specific Coefficient on chemical level
'Inputs: RecipeID; CoefficientID; ChemicalID
'Outputs: CoefficientValue for Chemical level
'***************************************************************************

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strsql As String
    Dim numcoef As Double

    Set db = CurrentDb()

    'sql string containing function inputs
    strsql = "SELECT tblLinkCoefficients.RecipeID, tblLinkCoefficients.ChemicalID," _
    & "tblLinkCoefficients.CoefficientID, tblLinkCoefficients.CoefficientValue " _
    & "FROM tblLinkCoefficients " _
    & "WHERE (((tblLinkCoefficients.RecipeID)=" & strrecid & ") " _
    & "AND ((tblLinkCoefficients.ChemicalID)=" & strchemid & ") " _
    & "AND ((tblLinkCoefficients.CoefficientID)=" & strcoefid & "))"

    Set rs = db.OpenRecordset(strsql)

    'Check if recordset is not empty, then set coef value
    If rs.EOF = False Then
        numcoef = rs("CoefficientValue")
    Else
        numcoef = 0
    End If

    'Reset recordset
    rs.Close
    Set rs = Nothing

    getCoefC = numcoef

End Function

现有qryA查询语句 #

SELECT tblProducts.ProductName, tblRecipes.RecipeName, tblChemicals.ChemicalName, 
tblRecipesChemicalsLink.WeightMeasureBefore, tblRecipesChemicalsLink.WeightMeasureAfter, 
tblRecipes.RecipeID, tblRecipesChemicalsLink.ChemicalID, getCoefC([tblRecipes].[RecipeID],
[tblRecipesChemicalsLink].[ChemicalID],23) AS CCoef
FROM (tblProducts INNER JOIN tblRecipes ON tblProducts.ProductID = tblRecipes.ProductID) 
INNER JOIN (tblChemicals INNER JOIN tblRecipesChemicalsLink ON tblChemicals.ChemicalID = 
tblRecipesChemicalsLink.ChemicalID) ON tblRecipes.RecipeID = 
tblRecipesChemicalsLink.RecipeID;

当前调用与需求 #

当前调用方式：

CCoef: getCoefC([tblRecipes].[RecipeID];[tblRecipesChemicalsLink].[ChemicalID];23)

期望简化为理想形式：

CCoef: getCoefC(23)

或次理想形式：

CCoef: getCoefC(RecipeID;ChemicalID;23)

尝试硬编码表引用到函数SQL中时，出现3061: too few parameters错误；直接使用RecipeID会因多表存在同名字段导致歧义。

解决方案 #

方案1：实现理想调用形式（仅依赖当前查询上下文） #

修改getCoefC函数，利用Access的Eval函数从查询上下文自动获取RecipeID和ChemicalID的值，前提是查询中必须包含这两个字段（当前qryA已包含）：

Public Function getCoefC(coefid As Integer) As Double
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strsql As String
    Dim numcoef As Double
    Dim currentRecipeID As Integer
    Dim currentChemicalID As Integer
    
    ' 从查询上下文获取字段值，字段名需与查询中一致
    currentRecipeID = Eval("RecipeID")
    currentChemicalID = Eval("ChemicalID")
    
    Set db = CurrentDb()
    
    ' 简化SQL语句，仅查询需要的字段
    strsql = "SELECT CoefficientValue FROM tblLinkCoefficients " & _
             "WHERE RecipeID = " & currentRecipeID & " " & _
             "AND ChemicalID = " & currentChemicalID & " " & _
             "AND CoefficientID = " & coefid
    
    Set rs = db.OpenRecordset(strsql)
    
    ' 用IIf简化空值判断
    numcoef = IIf(rs.EOF, 0, rs("CoefficientValue"))
    
    ' 释放资源
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    getCoefC = numcoef
End Function

修改后qryA中的调用语句即可简化为：

CCoef: getCoefC(23)

注意：此函数仅适用于包含RecipeID和ChemicalID字段的查询，移植性有限。

方案2：实现次理想调用形式（简化参数为字段名） #

若希望函数保留一定通用性，可通过给查询中的字段添加别名来消除歧义，再直接传入别名作为参数：

1. 修改qryA的字段列表，给重复字段添加别名：

SELECT 
    tblProducts.ProductName, 
    tblRecipes.RecipeName, 
    tblChemicals.ChemicalName, 
    tblRecipesChemicalsLink.WeightMeasureBefore, 
    tblRecipesChemicalsLink.WeightMeasureAfter, 
    tblRecipes.RecipeID AS CurrentRecipeID,  ' 添加别名
    tblRecipesChemicalsLink.ChemicalID AS CurrentChemicalID,  ' 添加别名
    getCoefC(CurrentRecipeID, CurrentChemicalID,23) AS CCoef
FROM ... ' 其余JOIN逻辑不变

2. 函数无需修改，调用时直接使用别名：

CCoef: getCoefC(CurrentRecipeID;CurrentChemicalID;23)

此方式既简化了调用语句，又保留了函数的通用性，适用于不同查询场景。

方案3：用JOIN替代函数调用（最优雅高效） #

逐行调用VBA函数在数据量大时效率低下，更优的方式是直接通过LEFT JOIN关联tblLinkCoefficients表，用Nz函数处理空值为0：

SELECT 
    tblProducts.ProductName, 
    tblRecipes.RecipeName, 
    tblChemicals.ChemicalName, 
    tblRecipesChemicalsLink.WeightMeasureBefore, 
    tblRecipesChemicalsLink.WeightMeasureAfter, 
    tblRecipes.RecipeID, 
    tblRecipesChemicalsLink.ChemicalID,
    Nz(lc.CoefficientValue, 0) AS CCoef  ' Nz将空值转为0
FROM 
    (tblProducts 
    INNER JOIN tblRecipes ON tblProducts.ProductID = tblRecipes.ProductID) 
    INNER JOIN (tblChemicals 
        INNER JOIN tblRecipesChemicalsLink ON tblChemicals.ChemicalID = tblRecipesChemicalsLink.ChemicalID) 
    ON tblRecipes.RecipeID = tblRecipesChemicalsLink.RecipeID
LEFT JOIN 
    tblLinkCoefficients AS lc 
    ON tblRecipes.RecipeID = lc.RecipeID 
    AND tblRecipesChemicalsLink.ChemicalID = lc.ChemicalID 
    AND lc.CoefficientID = 23;  ' 指定系数ID

若需要添加其他系数，只需新增一个LEFT JOIN即可：

LEFT JOIN 
    tblLinkCoefficients AS lcOther 
    ON tblRecipes.RecipeID = lcOther.RecipeID 
    AND tblRecipesChemicalsLink.ChemicalID = lcOther.ChemicalID 
    AND lcOther.CoefficientID = 45,
Nz(lcOther.CoefficientValue,0) AS OtherCoef

此方案无需依赖VBA函数，执行效率更高，可扩展性更强，是最推荐的解决方案。

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