calculated control stopped working

[Guest]

I originally had a field "hours worked" for each day of the week and a totals
field that was just day1+day2 etc. I changed it and added a start time and
end time for each day, and made the hours worked field a calculated control
(end-start*24) and that works fine, but the weekly total doesn't work
anymore. Any ideas?

If I change the hours worked field back to non calculated, the totals works
fine. Why would the weekly totals stop working? What syntax do I need to have
it work again?

tia

HB

 

[Wayne Morgan]

Before the hours worked was a calculated control, you had values in the
underlying recordset to total for the week. They are no longer there, you
just have a displayed total for each day. What you need now for the weekly
total is to total the same equation you're using for the daily hours.

What you may find works the best is to make the daily calculation a
calculated field in the query feeding the form rather than a calculated
control. Bind the daily hours control to this calculated field. Then you
should be able to get your weekly total to work by totaling this calculated
field just as you were previously totaling the field before the daily hours
was calculated.

 

[Guest]

Wayne, I tried it with one field and yes, the weekly total worked, but I also
have a monthly total (a Dsum on the field (now) bound to the query calculated
field) that still doesn't work. Ideas?

Thanks.

HB

 

[Wayne Morgan]

Will you post your DSum statement? Is it the Control Source of a textbox?

 

[Guest]

This is the calculated field for hours worked: It is not in the query but on
the control source: =NZ(([MondayEnd]-[MondaySt])*24,0) (Field name MonTOT)

Here is the DSum for all Monday hours for the month. It is also the control
source:
=NZ(DSum(" [MondayHrs] ","WORKING_MEMBER_HOURS","Month([WeekEndDate])=" &
Month([MondayDT]) & "And [MemberID]=" & [MemberID]),0)

In the Dsum, MondayHrs is the field bound to the table. In order to make
this work, the user copies the hours calculated in MonTot (above) into the
MondayHrs field. The DSum does not work if I Dsum the MonTot Field.

I have tried moving the calcluation to the query as well and it hasn't
worked either.

H

 

[Guest]

I forgot to add, if I set the control source of MondayHrs to MonTot, either
as a calc. field or in the query, the Dsum doesn't work. That is the
fundamental problem.

 

[Wayne Morgan]

The DSum will only work on a recordset, not on controls on a form. The
"field" you call MonTOT isn't a field, it should be the name of the textbox
on the form. A field is a member of a recordset. A recordset comes from a
table or query. This is frequently confused because a recordset is assigned
as the Record Source for a form and then the controls on the form are bound
to fields in the recordset.

The advantage of doing the calculation in the query is that you then have a
field upon which DSum can work. (As a side bar, if you do this in a report,
the textboxes in a report have a running sum option). So, if you created a
field called MonTOT in the query feeding the form, the Field Name box would
look like this in the query design grid:

MonTOT:Nz(([MondayEnd]-[MondaySt])*24,0)

You would then set this field as the Control Source of your MonTOT textbox
on the form. You could also then amend your DSum statement to:

=DSum("[MonTOT]","NameOfQueryFeedingTheForm","Month([WeekEndDate])="
& Month([MondayDT]) & " And [MemberID]=" & [MemberID])

(all on one line, of course). The Nz shouldn't be needed here, because the
field is defined (first example) using Nz already. So there shouldn't be any
Null values.

You may also be able to replace the DSum statement above with

=Sum([MonTOT])

depending on the filters being applied to the form. Otherwise, you'll need
the filters in the DSum statement.