Calculating totals in continous form

[Moss100]

Hello all,

I have a continuous form with a textbox called CONFIRMED_SUM

The textbox is bound to the field ATTENDEE_CONFIRMED

In the footer of the form I have a textbox with the control source set as :

Sum([attendee_confirmed])

This gives a total for the form with is fine.

The problem arises when there are no records in the form. If there are no records I would like the calculated text box to state 0

I think I need to use NZ in someway - but can't figure out how.

Any help much appreciated.

Regards

Mark

 

[PHV]

Nz(Sum([attendee_confirmed]), 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thanks, but this only works when records are present - if there are no records on the form the text box remains empty.

Any further ideas?

Regards

Mark

 

[PHV]

Set a DefaultValue ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Set a DefaultValue ? - No this doesn't work either - very frustrating!

 

[PHV]

Perhaps a format like this ?
0;(0);0;0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Change the control source to:

=IIf(Count(*)=0,0,Sum([attendee_confirmed]))

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I like PH's use of the format property since it doesn't change the control source.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

The format solution kind of works, but only when the control has focus.

The other code does not work until the form has records in it.

The problem seems to be setting the text box whilst there are no records in the form - it's not records present but with null values, but rather no records present.

Funny how seemingly simple things are sometimes the hardest to solve!!

Thanks do far everyone.

 

[PHV]

Funny how seemingly simple things are sometimes the hardest to solve!!
You know what ? I wonder what is the problem to have an empty textbox when where is nothing to display ...

 

[Moss100]

I'm using the form as a sub form.

The totals work fine once records are inserted, but if there are no records I just wanted the text box to display 0 instead of being empty.



Regards mark.

 

[dhookom]

It might have helped if you had suggested the form was used as a subform. Does your subform allow additions?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thanks for the interest - the form (although used as a subform) reacts the same when just used as a form.

I have programmed around it by using a record count field that displays 0 when there are no records - but I would still be interested to know whether it is possible to set a textbox to display 0 when used to sum on a contuous form.

Thank you very much for all your suggestions anyhow.

 

[dhookom]

If I had known this was on a subform, I would have suggested placing the totals on the main form.

Duane
Hook'D on Access
MS Access MVP