DSUM in Access query yields Nulls in random record

Question

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())

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

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.

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] &"'")

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

Then I tried:

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

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.

I never figured out why DSUM() did this.

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

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.

Answer 1

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 

...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 

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] &"'")