Getting #Error, nothing, and a delay for =Sum([field]) in a subfor

[RichKorea]

I'm trying to get a total using a text box for one of the fields in a form
using =Sum([TaskTime]), but no matter what I try, I get either #Error or just
a blank text box.
The text box is in the Form Footer
If I remove "Sum" so the formula reads =([TaskTime]), I get the value for
the current record (no error)
I compared all of the properties for my form against the Northwind Orders
Subform, and anything that's different, I changed the setting on my form to
see if it made any difference - it didn't.
The subform is based on a query that calculates the time delta (EndTime -
StartTime), so it is based on a bound field.
I also created a new subform (which of course doesn't have everything setup
the way I want it), and it doesn't show the total (just a blank text box)
until I use the navigation buttons to create a new record in the subform, and
then the total shows up in the new subform.

Is there some setting or something that could be causing these types of
problems (no display or displaying an #Error, and a display getting triggered
by creating a new record on the subform)? I went through all of the threads
under forms that came up for a search on subform #Error sum, but I didn't see
anything that appeared to match my problem. I'm using Access 2003 SP2.

Thanks,
Rich

 

[code_hungry]

RichKorea,

try:
=nz(Sum([TaskTime]))

it sounds to me that your table has Null values. Access needs to convert
lack of values into zeros before is able to make calcualtions. nz does just
that; it converts nulls into zeros.

see if it works

 

[RichKorea]

I figured it out - It wasn't Null values, although I've added Nz to the
formula to be safer. I had two errors going on.

First was my total field in the Footer was referencing the sub-total field
in the Detail section, which was connected to the sub-total field in the
query. I changed the total field to sum the query field directly, which
eliminated the error.

The calculated fields on the form staying blank appears to have been caused
by the Timer Interval value being set to "1", rather then "0" (my code
modifies the timer to handle a problem with wanting to hide a control after
it looses focus, so I just need to make sure the timer value is set back to 0
for all cases).

RichKorea,

try:
=nz(Sum([TaskTime]))

it sounds to me that your table has Null values. Access needs to convert
lack of values into zeros before is able to make calcualtions. nz does just
that; it converts nulls into zeros.

see if it works
--
I feel the need....the need for code!

I'm trying to get a total using a text box for one of the fields in a form
using =Sum([TaskTime]), but no matter what I try, I get either #Error or just
a blank text box.
The text box is in the Form Footer
If I remove "Sum" so the formula reads =([TaskTime]), I get the value for
the current record (no error)
I compared all of the properties for my form against the Northwind Orders
Subform, and anything that's different, I changed the setting on my form to
see if it made any difference - it didn't.
The subform is based on a query that calculates the time delta (EndTime -
StartTime), so it is based on a bound field.
I also created a new subform (which of course doesn't have everything setup
the way I want it), and it doesn't show the total (just a blank text box)
until I use the navigation buttons to create a new record in the subform, and
then the total shows up in the new subform.

Is there some setting or something that could be causing these types of
problems (no display or displaying an #Error, and a display getting triggered
by creating a new record on the subform)? I went through all of the threads
under forms that came up for a search on subform #Error sum, but I didn't see
anything that appeared to match my problem. I'm using Access 2003 SP2.

Thanks,
Rich