Access中DLookup按日期条件查询价格返回错误结果求助

问题详情 #

使用以下DLookup表达式从query1查询中获取对应价格时，返回结果不符合预期：

Expr1: DLookUp("[price]","[query1]","[converted]<=" & [converted] & " And [item]='" & [item] & "'")

其中converted字段是通过CDbl([date])转换而来的日期值（原日期格式为dd/mm/yyyy，之前因日期条件写法错误改用CDbl转换）。

源数据（query1） #

测试结果 #

查询item='aa'且date='26/10/2022'时：

• 预期返回价格：7（对应aa的23/10/2022记录，是小于等于26/10/2022的最近日期）
• 实际返回价格：9（对应aa的30/10/2022记录）

问题原因 #

DLookup在匹配到多条符合[converted]<=[当前值]且item匹配的记录时，不会自动按日期排序取最近的一条，而是返回数据存储物理顺序中的最后一条匹配记录，这就是为什么会返回30/10/2022的价格。

解决方法 #

方法1：修改DLookup表达式，指定取最大converted对应的价格 #

通过嵌套子查询先找到符合条件的最大converted值，再获取对应价格：

Expr1: DLookUp("[price]","[query1]","[converted]=(SELECT Max([converted]) FROM [query1] WHERE [item]='" & [item] & "' AND [converted]<=" & [converted] & ") AND [item]='" & [item] & "'")

方法2：预创建分组查询，再用DLookup取数 #

先创建一个按item分组、取每个item下小于等于当前日期的最大converted对应价格的查询（命名为query_latest_price），SQL语句如下：

SELECT q1.item, q1.price
FROM query1 AS q1
INNER JOIN (
    SELECT item, Max(converted) AS max_conv
    FROM query1
    GROUP BY item
) AS q2 ON q1.item = q2.item AND q1.converted = q2.max_conv
WHERE q1.converted <= [当前查询的converted字段]

之后修改DLookup表达式从这个新查询取数：

Expr1: DLookUp("[price]","[query_latest_price]","[item]='" & [item] & "'")

额外检查：确保日期转换正确 #

如果原始date字段是文本类型，CDbl([date])可能因日期格式识别错误导致converted值异常。可以先将文本日期转为正确的日期类型再转Double：

converted: CDbl(CDate(Format([date], "dd/mm/yyyy")))

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