Is this a bug in WorksheetFunction.Dsum ?

**jdwalsh** · 03-09-2013, 02:39 AM

I have found that the WorksheetFunction.DSum fails to work when the criteria block contains a condition on a date field.

I have created a testcase using a toy data set of 10 rows and 5 fields. I display 2 versions of the criteria block, one filled in from the keyboard, the other generated by my macro. Results are displayed for WorksheetFunction.DSum and excel's DSUM , both functions are run with each version of the criteria block. My findings are ;

DSUM always works
DSum works if date fields are not used in criteria
DSum works if date field criteria is used and date has mm=dd
DSum does not work with a criteria on a general date value

This obviously points to a problem interpreting the date format. I have tried many different ways to set the date value in my macro e.g #mm/dd/yy#,As Date, CDate(). In all cases, the results remain the same and the macro generated criteria block looks identical to the keyboard entered one.

I will gladly post my test code (macro or spreadsheet) if it will be helpful. I would appreciate hearing from anyone who has experienced the same problem or who has a solution on how to handle date fields properly in a WorksheetFunction.DSum criteria block

**:) Sixthsense :)** · 03-09-2013, 02:44 AM

It will be easy to understand if you show something in excel

**jdwalsh** · 03-13-2013, 04:30 PM

I solved my problem with WorkFunction.DSum by carefully matching the format of a date field with the target database using the excel Text function.

The problem was obfuscated by the fact that the excel DSUM function seemed to work on examples of criteria blocks where DSum failed. And of course DSum did work on some examples.

I developed the test harness test_dsum to methodically explore some simplified test data. The ability to organize and record results soon led to the solution.

The description from test_dsum documents the testing ;

Please Login or Register  to view this content.

I am attaching the test_dsum source code because I think it does a good job of managing simple testing, and it could be adapted to other test problems