Calculated Text Box shows #Error - form based on crosstab query

[phinix]

Hello everyone!
this is my first post here - for a long time this forum was a great source of knowledge for me, so decided to register

My problem:
I have a form that shows results of crosstab query.
In Detail section there are students names, their exam results, summary of results for each student and count of subjects each student took part - all from query. Then there are calculated text boxes that use those exam results, summaries and counts to calculate residuals for each subject.
Form footer is the place where the problem happens - I built text box that calculates average of those residuals and as long as there are residuals above in Detail section, this box works, but if there is a subject that noone took part and its residual box is Null - all my text boxes in form footer change to #Error

Pleas ehelp me out here, cause I tried lots of things, nothing worked, but I'm sure there is a way to go around this issue.

Let me know if you need any more details!
Thank you!

 

[DCrake]

You may find that you need to incorperate NZ() around fields that could have null values in them. Without seing your calculation it is hard to accurately diagnose the issue.

 

[phinix]

You may find that you need to incorperate NZ() around fields that could have null values in them. Without seing your calculation it is hard to accurately diagnose the issue.

Hi - thanks for reply!

To show the text box , this is the formula for those calculated averages in the bottom of the form:

=Avg((IIf([SubjectCount]=1,Null,(IIf(IsNumeric([Art]),(([Summary]-[art])/(Nz(([SubjectCount]-1),1))-[art]),Null)))))


Here is the form screenshot to make it more visible....

 

[DCrake]

=Avg((IIf([SubjectCount]=1,Null,(IIf(IsNumeric([Art]),(([Summary]-[art])/(Nz(([SubjectCount]-1),1))-[art]),Null)))))

You are saying, if I am reading it right

If Subjectcount = 1 then
  Null
Else
   If IsNumeric(Art) Then
       (Summary-Art)/(subjectcount-1)-Art
   Else
     Null
End If

You cannot peform an average of Null So if there is no subject count.

 

[phinix]

Thing is, this is the formula for average of that art. Box above it, in detail section is the art residual that is shown for each student, called ArtRes. I wanted to get Avg of each subject, but as soon as I easily use "Avg([ArtRes])" it comes with #Error. So what I had to do is I copied the whole formula of this ArtRes box to this form footer average box. Now it shows average ok, but when I use this formula for each subject it shows #Error, cause two of the subjects above are empty (none of the students took that exam).
Now, as you can see I used IIF and changed every subject that has no result to Null, that is why right side of the form shows all results ok and for subjects that don't have results, it leaves their residuals empty.
For example now you can see two subjects like that on the left with green triangle - Med and Pro. These left bands results are values form crosstab query, I added them in form manualy, there are not dynamic, so if crosstab query doesnt have values for them they come out as errors.
I managed to changed them to Null in right side part in residuals but for some reason it doesnt work for form footer...

 

[phinix]

=Avg((IIf([SubjectCount]=1,Null,(IIf(IsNumeric([Art]),(([Summary]-[art])/(Nz(([SubjectCount]-1),1))-[art]),Null)))))

You are saying, if I am reading it right

If Subjectcount = 1 then
  Null
Else
   If IsNumeric(Art) Then
       (Summary-Art)/(subjectcount-1)-Art
   Else
     Null
End If

You cannot peform an average of Null So if there is no subject count.

You're right, I cannot use Sum or Avg or any arithmetic function on null..

So what can I do with it? It works if there even one subject, right? But how can I change it to show empty field for Nulls?
For some reason IsNumeric doens't work for that, which I thought it will.

 

[DCrake]

Can you post a copy of the db to look at?

 

[phinix]

Can you post a copy of the db to look at?

I'm affraid I can't
is ther anything you would like to see, maybe I could paste crosstab query, or some part of db?

 

[DCrake]

What is the control source of the residuals in the detail section?

 

[phinix]

What is the control source of the residuals in the detail section?

Basically its is the same as avg only without avg... for some reason avg box only works if I use full formula, not only name of the residual box from details section.. madness! two days I'm trying to make it work...

=IIf([SubjectCount]=1,Null,(IIf(IsNumeric([art]),(([Summary]-[art])/(Nz(([SubjectCount]-1),1))-[art]),Null)))

See, this formula for residual in detail section works fine.
When I add Avg to it and put in form footer it works as long as it gets full null column of some subject residual form above.
It works if some student didnt take place on some exam and for example some students have art result and have residual calculated for it, that means some of the residuals are Null, some not - formula works fine. Only if none of the students had that subject and whole column under for example art has Nulls.. then it shows #Error

 

[DCrake]

Do you really need it to be Null in the field? can you not simply let it be zero? Averaging only fields with values dos not produce the correct average you need it average the total number of lines.

If you can get it to hold a value in the detail you will be able to simplify your summary field.

 

[phinix]

Do you really need it to be Null in the field? can you not simply let it be zero? Averaging only fields with values dos not produce the correct average you need it average the total number of lines.

If you can get it to hold a value in the detail you will be able to simplify your summary field.

Problem is residual can be 0, in this case I won't know if that is real 0 or it is zero cause there was no result on left side of the form.

Let me show you what is residual:
For example student residual for Art would be: Average of all other exams of this student eccept Art - Art result.

That means I need to get:
[sum of results of all exams] - [Art result] / [Count of all exam results]-1,

that would be average of all results accept Art, then I would need to substruct Art result.
If student had 3 exams: Art , Math, English. Results were 1,1,1.
average of other exams would be 1+1/2=1. Now that minus Art result gives you 0. That is why I cannot use 0 for empty fields, cause then it will be calculated as residual...

 

[DCrake]

Try placing a hidden texbox in the detail section that has the art field as its control source and try and average that.

 

[phinix]

Try placing a hidden texbox in the detail section that has the art field as its control source and try and average that.

Sorry, what do you mean? there is Art field on the left, it has art result.

 

[phinix]

is there a way to calculate it like:

"for all residuals that are not null, do average"?

Is there a way to use FOR clause in expression builder?
I know there is DLook but that would have to base on table or query... is there any function that checkes form results?

 

[vbaInet]

is there a way to calculate it like:

"for all residuals that are not null, do average"?

Something like this perhaps?

=IIF(Not IsNumeric(Sum([Art_Residual])),Null, Avg())

 

[phinix]

Something like this perhaps?

=IIF(Not IsNumeric(Sum([Art_Residual])),Null, Avg())

I tried it as well, but didn't work

There must be some rule for Access that I'm missing...

See, for some reason if just one calculated text box gives error, then all boxes in this form turns to errors as well! What is that?!

 

[vbaInet]

Can you show exactly how you wrote the statement?

 

[phinix]

Can you show exactly how you wrote the statement?

=IIf(IsNumeric([artres]),Avg([artres]),0)

 

[phinix]

OK. I've been thinking about this and I think there is no way to fix it.
Basically because avg can be done on numerical values and I cannot put "0" in null fields. All null fields needs to be null, cause if I put "0" in it it will be taken as zero, which would mean something for average.

How can I do the avg function skipping all null fields?