Is this a bug in WorksheetFunction.Dsum ?

[jdwalsh]

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 :)]

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

[jdwalsh]

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 ;

'   FILENAME
'   Test_dsum.xls
'
'   DATE
'   07-Mar-13   Developed
'
'   DESCRIPTION
'   A test harness to compare the results of Excel's DSUM function with WorksheetFunction.DSum
'   The development was instigated by  noticing surprise results from an application using DSum
'   The situation was made more confusing by the fact that DSUM seemed to be working on the same
'   criteria block where DSum was failing. An organized way to run and record tests was needed
'   and it quickly led to the situation being resolved. The following tests are shown here ;
'       Test1   The criteria block contains conditions on a date field, DSUM works, DSum fails
'       Test2   The date field is chosen with the format dd=mm, DSUM works, DSum works
'       Test3   Only non-date fields are used in the criteria block, DSUM works, DSum works
'       Test4   Rerun Test1 using the function Text to format date field, DSUM works,DSum works
'               with generated criteria (left) but still fails with keyboard entered (right)
'   Conclusion: When handling date fields in a DSum criteria block, the format must be carefully
'   chosen to match the database fields using the excel Text function
'
'   NOTES ON THE CODE
'   1. Each test generates its own worksheet, which contains a test description, timestamp
'      and the results matrix
'   2. A test may be rerun indefinitely. The timestamp will change and optionally the description
'   3. The colour in the results matrix gives a quick automatic confirmation of the results
'   4. The test name is automatically chosen by the software
'   5. There is no limit on the number of tests. Adding a new test is easy. Supply a description and
'      add a new Case branch where indicated. You will be notified if you forget.
'   6. All the code is contained in Module1 and the code page of UserForm1
'   7. Runs without change on Excel 2003 and Excel 2010 under Windows 7 and XP
'   8. This test harness could be easily adapted to handle many other test scanarios
'
'   AUTHOR
'   Desmond Walsh, Ottawa, Canada
'
'   ACKNOWLEDGEMENTS
'   Some of the code was taken from samples provided in ;
'       Excel 2007 Power Programming with VBA   John Walkenbach
'   An excellent reference !

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