Calculated field (SUM) in a sub-form gives #ERROR occasionally

[sudakov]

Hello everyone,
I have a continuous sub-form within a form. Both forms are bound. The sub-form has a text-box control "TXB_AGGREGATE" that calculates Totals of the sub-form (=SUM([FIELD1]*[FIELD2]) in its Footer section.

The Main- and Sub-form are related using LINK MASTER/CHILD FIELDS properties.

When a user moves through records in Main-form, its sub-form occasionally gives #ERROR in the control "TXB_AGGREGATE".

If the user moves back and forth one record, the #ERROR disappears.

Does anybody know how to get around this problem?

Sudakov.

 

[TheAceMan1]

How are ya sudakov . . .

Try:

[blue]=SUM(Nz([FIELD1],0)*Nz([FIELD2],0))[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[sudakov]

Hi TheAceMan1,

I am using =SUM(nz([FIELD1],0)*nz([FIELD2],0)

I am sorry for not being exact by omitting NZ() in my description.

There is probably something else causing #ERROR.

 

[SkipVought]

If the user moves back and forth one record, the #ERROR disappears.

What exactly, are the two values that you are summing, in the row where the error occurrs?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...and what are the data types of each of these two fields?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sudakov]

Hi SkipVought,

Values could be different.
The expression in the calculated control "TXB_AGGREGATE" actually is:

=Sum((nz([FIELD1],0)*nz([FIELD2],0)-nz([FIELD3],0))*nz([FIELD4],0))

WHERE the types are:
FIELD1 - Currency;
FIELD2 - Long Integer;
FIELD3 - Currency;
FIELD4 - Double.

 

[SkipVought]

Again, PLEASE answer the question...

What exactly, are the [red]values[/red] that you are summing, [red]in the row where the error occurrs[/red]?




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sudakov]

Hi SkipVought,
sorry for that.

For example, the Main form has 8 records. When a user positions (using Standard Navigation Buttons) on the last record on the Main form, the sub-form has one record where the values are as follows:
FIELD1 = 50,000.00 (Currency);
FIELD2 = 1 (Long Integer);
FIELD3 = 0 (Currency);
FIELD4 = 1 (Double).

The calculated control "TXB_AGGREGATE" with formula:

=Sum((nz([FIELD1],0)*nz([FIELD2],0)-nz([FIELD3],0))*nz([FIELD4],0))

displays #ERROR.

When a user moves one record backwards (record 7) on the Main Form, the #ERROR in the sub-form disappears. Then the user moves forth to record 8 on the Main Form again, the #ERROR in the sub-form is gone (value of $50,000.00 is displayed).