Access查询中的DSUM在随机记录中产生Null [英] DSUM in Access query yields Nulls in random records

问题描述

我有一个查询(在MS Access 2013中)，该查询提供了下一个12天内每天按日期列出的各种商品的销售额.在另一个表格中，我为每个商品提供了已知的采购提前期，例如30到90天. 我创建了一个查询，并针对每个商品，根据提前期使用以下方法计算了将来的日期:

I have a query (in MS Access 2013) that gives me the Sales for various Items by Date, for each day for the next 12mo. In another table, I have each Item with its known purchasing leadtime, say between 30 and 90 days. I created a query, and for each Item, I calculated the future date according to the leadtime, using:

FutureDate: DateAdd("d",[Leadtime],Date())

我验证了Sales查询中是否存在所有Items，并且所有FutureDate都位于Sales中存在的记录中.

I validated all Items exist in the Sales query, and all FutureDates are within the records that exist in Sales.

我需要计算从现在开始到每个项目的[FutureDate]之间的每日销售额的总和，以获取每个项目唯一的交货时间内预期的总销售额.

I need to calculate the sum of daily Sales between now and the calculated [FutureDate] for each Item, to get the total amount of Sales expected within the unique Leadtime of each item.

我尝试了具有奇怪结果的函数DSUM():

I tried function DSUM() with weird results:

每日销售额"查询已经排除了任何过去的销售额，因此我的第一次尝试是:

The query of daily Sales already excludes any past sales, so my first try was:

TotalSalesInLeadtime: DSUM("DailySales","Sales","[DayOfSale]<=#" & [FutureDate] & "# AND [Item]='" & [SearchedItem] &"'")

对于某些项，[TotalSalesInLeadtime]正确计算，而其他项评估为Null.

For some Items, [TotalSalesInLeadtime] calculated correctly, while others evaluated to Null.

然后我尝试:

TotalSalesInLeadtime: DSUM("DailySales","Sales","[DayOfSale] BETWEEN #" Date() "# AND #" & [FutureDate] & " AND [Item]='" & [SearchedItem] &"'")

现在，结果被颠倒了.现在，对于以前显示为Null的项目，[TotalSalesInLeadtime]值正确显示，对于先前正确评估的项目，其值为Null.

The results now were reversed. [TotalSalesInLeadtime] values now showed correctly for the items that previously showed Null, and were Null for items that previously evaluated correctly.

我从不知道为什么DSUM()会这样做.

I never figured out why DSUM() did this.

要解决DSUM()的故障，我使用了一个嵌入式子查询，该子查询可以正确地产生所有值，尽管对性能的影响很大:

To work around the DSUM() glitch, I went with an embedded subquery, which yielded all the values correctly, albeit at a significant performance hit:

SELECT [PurchItem],
        (SELECT Sum([DailySales]) AS SumOfSales
         FROM [Sales] 
         WHERE ([Item]=[LeadtimeItems].[PurchItem]) AND ([DayOfSale] Between Date() AND [LeadtimeItems].[FutureDate]))
        As TotalSalesInLeadtime
FROM LeadtimeItems

如果任何人都知道DSUM为何会以这种方式运行，那么我将不胜感激. DSUM正常工作时，速度似乎肯定会更快.

If anyone has a clue why DSUM may behave this way, I'd appreciate the help. DSUM, when it works, certainly seems to go faster.

推荐答案

当粘合在一起"的SQL语句(或片段)包含用井号(#)括起来的日期文字时，必须牢记Access SQL VBA始终将模糊的日期文字解释为mm-dd-yyyy，而不管系统范围的日期格式如何.因此，在将Windows配置为使用dd-mm-yyyy的计算机上，像4月30日这样的明确日期就可以了

When "gluing together" SQL statements (or fragments) that include date literals enclosed in hash marks (#), one must bear in mind that Access SQL and VBA will always interpret ambiguous date literals as mm-dd-yyyy regardless of the system-wide date format. So on a machine where Windows has been configured to use dd-mm-yyyy, an unambiguous date like April 30 will work okay

?DateSerial(2013,4,30)
30-04-2013 
?"#" & DateSerial(2013,4,30) & "#"
#30-04-2013#
?Eval("#" & DateSerial(2013,4,30) & "#")
30-04-2013 

...但是到第二天，即5月1日，一切都无法正常进行

...but for the next day, May 1, things don't work so well

?DateSerial(2013,5,1)
01-05-2013 
?"#" & DateSerial(2013,5,1) & "#"
#01-05-2013#
?Eval("#" & DateSerial(2013,5,1) & "#")
05-01-2013 

因此，经验教训是，我们每次必须粘合"日期文字时，都必须确保这些日期采用明确的格式，例如yyyy-mm-dd.关于这个特定问题，我们需要使用

So the lesson is that any time we "glue together" date literals we must ensure that those dates are in an unambiguous format like yyyy-mm-dd. With regard to this particular question, we need to use

TotalSalesInLeadtime: DSUM("DailySales","Sales","[DayOfSale]<=#" & Format([FutureDate], "yyyy-mm-dd") & "# AND [Item]='" & [SearchedItem] &"'")

这篇关于Access查询中的DSUM在随机记录中产生Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！