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