By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

DSum Issues

P: 8
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]","TimesheetQuery","[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
Jul 31 '08 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
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]","TimesheetQuery","[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)>
Jul 31 '08 #2

P: 8
Ok, so I tried putting this in

=DSum("DateDiff("n",[StartTime],[EndTime]","TimesheetQuery","[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?
Jul 31 '08 #3

P: 8
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!
Jul 31 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
Glad you got it working!

Linq ;0)>
Jul 31 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.