Calculated control not displaying zero when recordset is empty on

[Pete]

Re Access 2003

I have an unbound form with a calculated control in the subform header :-
ControlSource = Nz(Sum([InvAmount]), 0)

I am filtering the subform when the user selects a PO# from the combo on the
parent form:-
.Filter = "[PO_No]=" & intPO_No
.FilterOn = True

This accurately displays the value when there are records to sum.
When there are no records to display in the Detail section of the subform
(after the filter is applied), I would like the control to display 0, but
instead the control displays nothing (blank). As I have another filed which
calculates the difference between this field and another; this too then
displays blank instead of zero.

So how can I get a calculated control to display 0 instead of blank, when
there are no records in the Detail section?

Many thanks.
Pete.

 

[Jeff Boyce]

Pete

The form may not "realize" that something has changed. Take a look at
Access HELP for the .Recalc and the .Repaint commands (I can never remember
which one handles recalculating, but I have my hunch...<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pete]

Hi Jeff,

Many thanks for the reply.

I have tried ReCalc, Refresh, Requery, Repaint (ReDecorate ;o) )

None of which will display zero for the calculated control.

Pete

The form may not "realize" that something has changed. Take a look at
Access HELP for the .Recalc and the .Repaint commands (I can never remember
which one handles recalculating, but I have my hunch...<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Re Access 2003

I have an unbound form with a calculated control in the subform header :-
ControlSource = Nz(Sum([InvAmount]), 0)

I am filtering the subform when the user selects a PO# from the combo on
the
parent form:-
.Filter = "[PO_No]=" & intPO_No
.FilterOn = True

This accurately displays the value when there are records to sum.
When there are no records to display in the Detail section of the subform
(after the filter is applied), I would like the control to display 0, but
instead the control displays nothing (blank). As I have another filed
which
calculates the difference between this field and another; this too then
displays blank instead of zero.

So how can I get a calculated control to display 0 instead of blank, when
there are no records in the Detail section?

Many thanks.
Pete.

 

[Dirk Goldgar]

Re Access 2003

I have an unbound form with a calculated control in the subform header :-
ControlSource = Nz(Sum([InvAmount]), 0)

I am filtering the subform when the user selects a PO# from the combo on
the
parent form:-
.Filter = "[PO_No]=" & intPO_No
.FilterOn = True

This accurately displays the value when there are records to sum.
When there are no records to display in the Detail section of the subform
(after the filter is applied), I would like the control to display 0, but
instead the control displays nothing (blank). As I have another filed
which
calculates the difference between this field and another; this too then
displays blank instead of zero.

So how can I get a calculated control to display 0 instead of blank, when
there are no records in the Detail section?

I suspect this is a known bug in Access. See this link:

http://www.allenbrowne.com/bug-06.html

If it weren't for that bug, you could write this as your controlsource and
expect it to work:

=IIf([Form].[Recordset].[RecordCount]>0, Sum([InvAmount]), 0)

But the bug keeps pretty much *any* calculated value from displaying in the
form header or footer, when the form is empty and no records can be added.

Since this only manifests when the form (or its recordsource query) doesn't
allow additions, maybe you can get around in a kludgey way it by setting the
form's AllowAdditions property to True, but using code to prevent the
addition of a new record. That won't help you, though, if your recordsource
is nonupdatable.

If this is your problem, you could probably set up a separate text box on
your main form instead, setting its controlsource to a DSum expression that
picks up the filter from the subform.

 

[Jeff Boyce]

Pete

Another possibility is for you to force the recalculation, not in the
unbound control, but in, say, the AfterUpdate event of the control you need
to do the calculation. So the rough idea is that you will "push" the
calculated value into the unbound control, rather than have it "pull" to
calculate.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Many thanks for the reply.

I have tried ReCalc, Refresh, Requery, Repaint (ReDecorate ;o) )

None of which will display zero for the calculated control.

Pete

The form may not "realize" that something has changed. Take a look at
Access HELP for the .Recalc and the .Repaint commands (I can never
remember
which one handles recalculating, but I have my hunch...<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Re Access 2003

I have an unbound form with a calculated control in the subform header
:-
ControlSource = Nz(Sum([InvAmount]), 0)

I am filtering the subform when the user selects a PO# from the combo
on
the
parent form:-
.Filter = "[PO_No]=" & intPO_No
.FilterOn = True

This accurately displays the value when there are records to sum.
When there are no records to display in the Detail section of the
subform
(after the filter is applied), I would like the control to display 0,
but
instead the control displays nothing (blank). As I have another filed
which
calculates the difference between this field and another; this too then
displays blank instead of zero.

So how can I get a calculated control to display 0 instead of blank,
when
there are no records in the Detail section?

Many thanks.
Pete.

 

[Paul Shapiro]

The original expression Nz(Sum([InvAmount]), 0) might be failing because
Sum([InvAmount]) returns #Error instead of Null. Try without the NZ and see
what gets displayed. And Dirk has already explained why a common work-around
fails in Access 2007.

Allen Browne's suggestion for this issue was using a function as the Control
Source. Referring to frm.RecordsetClone.RecordCount or
frm.Recordset.RecordCount works fine in the code context. That worked well
for me in a similar situation.

Re Access 2003

I have an unbound form with a calculated control in the subform header :-
ControlSource = Nz(Sum([InvAmount]), 0)

I am filtering the subform when the user selects a PO# from the combo on
the
parent form:-
.Filter = "[PO_No]=" & intPO_No
.FilterOn = True

This accurately displays the value when there are records to sum.
When there are no records to display in the Detail section of the subform
(after the filter is applied), I would like the control to display 0, but
instead the control displays nothing (blank). As I have another filed
which
calculates the difference between this field and another; this too then
displays blank instead of zero.

So how can I get a calculated control to display 0 instead of blank, when
there are no records in the Detail section?

I suspect this is a known bug in Access. See this link:

http://www.allenbrowne.com/bug-06.html

If it weren't for that bug, you could write this as your controlsource and
expect it to work:

=IIf([Form].[Recordset].[RecordCount]>0, Sum([InvAmount]), 0)

But the bug keeps pretty much *any* calculated value from displaying in
the form header or footer, when the form is empty and no records can be
added.

Since this only manifests when the form (or its recordsource query)
doesn't allow additions, maybe you can get around in a kludgey way it by
setting the form's AllowAdditions property to True, but using code to
prevent the addition of a new record. That won't help you, though, if
your recordsource is nonupdatable.

If this is your problem, you could probably set up a separate text box on
your main form instead, setting its controlsource to a DSum expression
that picks up the filter from the subform.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)