Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

sudakov

Programmer
Jun 17, 2007
53
US
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.
 
How are ya sudakov . . .

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
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.
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 


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

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

Values could be different.
The expression in the calculated control "TXB_AGGREGATE" actually is:
Code:
=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.
 


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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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:
Code:
=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).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top