Too complex? (1 Viewer)

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
I have a report which displays a field called "Mod Std Set Up" from a Query. When I try to sum this field in the report footer by using =Sum([Mod Std Set Up]) I get a system message stating that either the expression is mis-spelled (which I've checked a thousand times) or it is too complex and needs simplifying.

Anyone have any ideas??
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
Does the field [Mod Std Set Up] get its vallue from a table or from a user defined query function. If the latter, the calculation might not pass properly and I'd suggest moving the calculation out of the query and onto the form in an unbound box.
Then try summing the box.

If the first option is true and the value comes from a field, check that the field is set up as a number/integer field. If it isn't make sure that the entries are not textual.The computer has problems as you can imagine summing text like "nothing","zero" etc...

Ian
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
Actually now that I've looked at the number this is displaying, it is wrong. The number is calculating correctly but it is not equal to the sum of the feild.

This is basically what's happening.
I have 3 feilds calculating a fourth, say A, B, C and D where D = C x (B/A).

The sum of D alone is quite different to sum[C] x (sum/sum[A]).

Where to now??
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
When faced with a problem like this I always sum everything separately using numerous unbound text boxes in the footer of the form.

This usually identified the culprit.
If all works well then you can just hide the unbound controls in the footer.
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
What I meant to say and didn't(doh!) is that I sum everything at the bottom then use the unbound text box names in the formulas instead of the formulas. This normally identifies if there is a logic problem or the info isn;t summing properly.

Ian
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
This is exactly what I was refering to. I had the detail of A, B, and C hidden in the report and then in a footer summed each induvidually. But using these sums to calculate D gives a different number than if you added up the detail of D.
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
Sounds like a logic problem to me.
Does it work properly if you only have 1 record in the form? If so then it sounds like the calculations need to be done per record and then summed rather than summed then equated. Just a thought because I don;t actually know what you are trying to achieve.

Ian
 

Chris RR

Registered User.
Local time
Today, 13:30
Joined
Mar 2, 2000
Messages
354
Not to disrupt the good dialogue here, but I have also seen this message come up when there is bad data in the table...
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
This calculation is performed per record but I don't know how to sum it.
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
If the sum needs doing per record then summing the way to do it is:

Put an unbound box in each record that performs the calculation called [rowcalc] which = C x (B/A).

Then in the footer have another unbound control called rowcalcsum which =sum([rowcalc]).

I think that should work.

Ian
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
I tried this several times and it doesn't work. I keep getting prompted for a value for [rowcalc] as I open the report.
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
Have you put the calculation in the default value property and disabled/locked the box?
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
I don't think those are applicable in a report.

Or am I looking in the wrong place?
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
Sorry Darrell, lost my head for a moment, thought we were in the forms forum! I'm going to knock up a rough version and see if I can replicate the problem you're having. I may be a couple of days so hang o...
 

Fornatian

Dim Person
Local time
Today, 18:30
Joined
Sep 1, 2000
Messages
1,396
One way I've worked round it is to put a copy of the calculating field in each row and set the running sum property to 'over group'.
May be a workaround.

Just as a side note I replicated your problem the first time I tried to do it aswell.

[This message has been edited by Fornatian (edited 02-13-2001).]
 

Darrell

Registered User.
Local time
Today, 18:30
Joined
Feb 1, 2001
Messages
299
This method was a complete success.

The running sum property of the calculated field copy was set to overgroup and hidden in the report.

Then in the On_Format property of the Group Footer the field for the Sum[D] was set to equal the running sum value.

I then created a copy of the Sum[D] and set that to overgroup also. I then used this in a similar fashion as above to get a report total for this field.

Thanks very much Ian for your time and persistance.
 

Users who are viewing this thread

Top Bottom