DSum error on form date parameters

[meldrape]

hi there,

I have the following statement:
=DSum("[HOURSBYTASK]","qyJobTracking","[TASKGROUP] = 'SOLIDBUILDER' and DENDEST BETWEEN #09/01/2002# AND #10/01/2002#&quot

This works great. If I replace the dates with forms!frmJobTracking!txtStart and forms!frmJobTracking!txtEnd I get an error. Ultimately, I would really like this in a query but I can't make it work in there either. It's not formatting the date correctly. I used this:
for the DENDEST field Between [forms]![frmJobTracking]![txtStart] And [forms]![frmJobTracking]![frmEnd]
The expresseion is typed incorrectly or is too complex to be evaluated, but if I put slashes between my dates, it works. I'm lost. Any help would be appreciated. Thanks.
P.S. Works with VBA though and the where condition in the open report command without formatting the date. Go figure.

 

[pdldavis]

Hi, what you have there should work in a query. You might check to ensure your text input boxes are formatted for dates. I seem to remember that being a problem. I'd try formatting the text input boxes as medium dates and use an input mask for same. Here's the sql for a query I used which worked:

SELECT Table1.Date, Table1.Num, Count(DSum("[num]","table1","[date] BETWEEN forms!form2!txtStart and forms!form2!txtend&quot) AS Test
FROM Table1
GROUP BY Table1.Date, Table1.Num;

 

[meldrape]

I'll try that. Seems like I've tried everything, including # on either side. It's odd because I wasn't using the date parameters in the query until I needed some special totals at the bottom where I had to use the DSum. The VBA code works great. Thanks for your reply. I'll try the test.

 

[pdldavis]

Hope it does. I looked at some older stuff that didn't work and came across the date problem you described. The problem I had was in the text box formatting.
Good Luck!