DSUM over calculated control not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I had a field (hours worked) and had it Dsum'd for the month. I had to add
two fields start tim & end time and calculate hours worked. Now, the Dsum
isn't working.

I tried putting the calcuation in the query as an expression, then bind the
hours worked to the query field but that didnt help either. I even tried
me.refresh after update with no luck. Suggestions?

tia.

HB
 
HB said:
I had a field (hours worked) and had it Dsum'd for the month. I had to add
two fields start tim & end time and calculate hours worked. Now, the Dsum
isn't working.

I tried putting the calcuation in the query as an expression, then bind the
hours worked to the query field but that didnt help either. I even tried
me.refresh after update with no luck.
Click to expand...


DSum operates on **fields** in a table/query, not on form
**controls**.

Normally you would prefer to use Sum instead of DSum,
because it operates on **fields** in the form's RecordSource
table/query (where you do not have to worry about how the
form was filtered. Note that Sum is not aware of
**controls** on a form/report either.

It's not at all clear how "the month" fits into your
situation, but you should go ahead and calculate the hours
worked for each day(?) in the form's record source query.
Then you can use a text box in the form's header/footer
section to calculate a total. The text box's expression
would be like:
=Sum(hoursworked)
where hoursworked is the name of the calculated field in the
query.

Refresh is not useful here. If you need to make the total
reflect a change made to either the starttime of endtime
fields, then use a Requery instead.
 
I have to use DSUM because they also want monthly totals, not just weekly
(which I can sum) so I have to have a"where" clause for the date. The table
design is weekEndDate,<<Day>>start, <<Day>>end. Originally it was just
weekEndDate, <<Day>>HoursWorked.

Why is it better to put the calculation in the query and not on a control?
 
Not clear exactly what you're working with here, but maybe
this is what you want:

=DSum("[<<Day>>end] - [<<Day>>start]", " table",
"weekEndDate >= " & Format(monthstart, "\#m\/d\/yyyy\#"))

If that's not it, then please explain how you determine
month start, what the values are in the day start and end
fields and if this calculation is supposed to be restricted
to an individual or what?

In the usual situation, it's better to do the calculation in
the query because it allows you to use the aggregate
functions in the form/report (as well as filter and/or sort
on the calculated value). But you're calculation is not the
common case, so DSum is probably as good as you can do.
 
Back
Top