MS Access 对带行计数列的查询执行Dlookup返回空值问题咨询

用户需要用DLookup返回查询结果中某一列的所有值，由于DLookup仅支持返回单条结果，所以在查询中新增了行计数列，希望将行号作为DLookup的筛选条件。

涉及查询SQL #

SELECT tblAssets.SerialNumber, tblAssets.ID, tblAssets.AssetType, tblPlacing.Location, tblPlacing.PlacingStartDate, tblPlacing.PlacingEndDate, RowNumber([ID]) AS [NO]
FROM tblAssets INNER JOIN (tblLocations RIGHT JOIN tblPlacing ON tblLocations.LocationID = tblPlacing.Location) ON tblAssets.ID = tblPlacing.AssetID
GROUP BY tblAssets.SerialNumber, tblAssets.ID, tblAssets.AssetType, tblPlacing.Location, tblPlacing.PlacingStartDate, tblPlacing.PlacingEndDate, RowNumber([ID])
HAVING (((tblAssets.AssetType)=55) AND ((tblPlacing.Location)=[Forms]![FrmPPM]![TXTLocationID]) AND ((tblPlacing.PlacingEndDate) Is Null) AND ((ResetRowNumber())<>False));

异常表现 #

• 查询本身运行结果正常
• 执行以下DLookup代码返回Null：

Dim VAR1 as Variant     
VAR1 = DLookup("[SerialNumber]", "QRYPPM", "[NO] = 1")   

• 反向以SerialNumber为条件查询行号可正常返回结果：

VAR1 = DLookup("[NO]", "QRYPPM", "[SerialNumber] = '6501038'")

核心问题出在查询的HAVING子句中调用了ResetRowNumber()函数，DLookup每次执行筛选操作时都会重新运行整个查询，当按[NO]=1筛选时，查询会重新执行，行号会被ResetRowNumber()重置，导致匹配逻辑失效。而按SerialNumber筛选时，无论行号如何重置，符合SerialNumber条件的记录本身存在，因此可以返回对应行号。

方案1：先把查询结果存入临时表再查询 #

先执行一次查询把所有结果（包含生成好的行号）写入本地临时表，之后直接用DLookup查临时表即可：

' 第一步：生成带固定行号的临时表
CurrentDb.Execute "SELECT * INTO TMP_QRYPPM FROM QRYPPM"
' 第二步：查临时表获取对应行号的记录
VAR1 = DLookup("[SerialNumber]", "TMP_QRYPPM", "[NO] = 1")
' 使用完后删除临时表
CurrentDb.Execute "DROP TABLE TMP_QRYPPM"

方案2：改用Recordset遍历代替DLookup #

直接打开查询的Recordset，直接按索引取对应行的数值，不需要依赖行号列，更适合批量取所有列值的需求：

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("QRYPPM", dbOpenDynaset)
' 确认有记录再取值
If Not rs.EOF Then
    rs.MoveFirst
    ' 取第1行的SerialNumber，要取第n行就循环MoveNext n-1次
    VAR1 = rs!SerialNumber
End If
rs.Close
Set rs = Nothing

该方案不需要额外生成临时表，性能更优，直接遍历整个Recordset即可拿到所有查询结果。

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