DSum Issues

[Adam1331]

I've been trying to get the DSum function to work but I've been running into some roadblocks. What I'm trying to do is to total up times in a timesheet report into different departments. The report is based on a query that gathers all an employee's info. In that query, I have a calculated field which gives an employee's total time on a process.

(DateDiff("n",[StartTime],[EndTime]))/60

This gives the total time in hourly fractions. Now what I'm trying to do on thre report is to total up these times but also to divide them up between which departments they belong to based on a process code. Here's where I'm running into issues. This is the Dsum functon I've been putting in the Control Source for a TextBox:

=DSum("[Time]","TimesheetQue ry","[Process#] >= 3000 And [Process#] <=3501")

This is only one variation that I have used. I'm really more of a beginner with Access and am learning through trial and error. As I've been looking at different threads trying to see what might make this work, I've tried several other variations but nothing seems to come out right.

Is it because I'm trying to sum a calculated field? Is it because I'm using the function incorrectly or entering it wrong? When I go to the report I'm getting '#Error'. Thanks again for the help

[missinglinq]

I seldom use this kind of thing in my practice, but I think you've diagnosed your own problem! As best I can remember, in order to use Aggregate functions, such as DSum(), with calculated fields, you have to include the calculation expression itself, rather than just a field that holds the results of the calculation.

Also, you probably need to change the name of Process#. The # sign is used by Access to delimit dates, so a statement such as

=DSum("[Time]","TimesheetQue ry","[Process#] >= 3000 And [Process#] <=3501")

is apt to confuse the Access gnomes making them think that everything between the #'s is a date!

Welcome to Bytes!

Linq ;0)>

[Adam1331]

Ok, so I tried putting this in

=DSum("DateDiff ("n",[StartTime],[EndTime]","TimesheetQue ry","[ProcessNumber] >= 3000 And [ProcessNumber]<= 3500")

and received the error

The expression you entered contains invalid syntax
You may have entered an operand without an operator

Any advice?

[Adam1331]

Hey thanks again for the time and help missinglinq but I found a different way around the isssues by using IIF statements in the query.

Have a good one!

[missinglinq]

Glad you got it working!

Linq ;0)>