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

[vbaInet]

You can put zero in the original query before the calculation, but when performing an average it will include the zeros which would yield inaccurate results.

Post a stripped-down versio of your db and we'll have a quick look.

 

[phinix]

You can put zero in the original query before the calculation, but when performing an average it will include the zeros which would yield inaccurate results.

Post a stripped-down versio of your db and we'll have a quick look.

That is exactly my problem - how to calculate average skipping null fields...
I cannot change them to zeros, cannot change them to words...

P.S. what do you mean by stripped-down version? How can I do it?

 

[vbaInet]

You can do the calculation in a function and call the function in the query as an aliased field. I remember DCrake mentioning that. Let's have a look anyway.

Here's how you post a db:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[phinix]

You can do the calculation in a function and call the function in the query as an aliased field. I remember DCrake mentioning that. Let's have a look anyway.

Here's how you post a db:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

Oh, you mean just attach db file - really sorry, but I cannot do that, it is pretty much confidential. But I can add some parts of it, like queries, forms, etc. if that helps.

 

[vbaInet]

That's fine, I don't need the whole db. Create a small db and move the relevant bits into that one ensuring that there's some sample data to test with. Also include the relevant queries/forms.

Don't include sensitive information. Bogus data is fine.

 

[phinix]

Here it is, cut up like a small sausage

There is main form, choose "both" option for gender.
Now, you can there calculated field below on right that it has #error - it is basically same as field above only with AVG function.
How can I make it work?

 

[vbaInet]

Just had a look. It wasn't apparent that the Residuals text boxes are ALL calculated values on form level. The Avg() function placed at the footer of a form/report would perform calculations on what is in the recordset. In your case, art residual (for example) isn't a field in your recorset and as a result you cannot perform any summation or averages on that calculated value.

1. Create a query based on your crosstab (including all the fields)
2. Perform the residual calculations for each subject in the query (as aliased fields)
3. Set this new query as the Record Source of your form
4. Bind the residual fields to the residual controls
5. Avg() based on the residual fields

 

[phinix]

Just had a look. It wasn't apparent that the Residuals text boxes are ALL calculated values on form level. The Avg() function placed at the footer of a form/report would perform calculations on what is in the recordset. In your case, art residual (for example) isn't a field in your recorset and as a result you cannot perform any summation or averages on that calculated value.

1. Create a query based on your crosstab (including all the fields)
2. Perform the residual calculations for each subject in the query (as aliased fields)
3. Set this new query as the Record Source of your form
4. Bind the residual fields to the residual controls
5. Avg() based on the residual fields

I thought you gonna say that
But I have another question - see this avg feld at the bottom?
Delete other two and leave just for art (which have value in the band on the left part fo the form). You will see that avg field actually calculates this residual again inside of itself (basically formula from the right part of the form). Thing is it works fine as long as there is band result for subject on the left part of form. Do you know what I mean? For example art - it has bnad result on the left, so avg field works fine, however if only one of the avg fields will calculate avg for subject that doesn't have result on the left, then all of them will crash. Check it out - delete all eccept art avg. It will work fine. So how make it work for all subjects - how to go around instance where subject doesn't exist? I tried IIF,IsNumeric, IsError, ISNull, Nz.. nothing worked.
Should I do what you suggested? Make a query that will calculate residuals?
Wait... I just realised - I cant do that cause I cannot predict what subjects will appear as result in crosstab query - that is the problem.

 

[vbaInet]

Create static fields for all subjects in the crosstab query. That is,

PIVOT [FieldName] In ('Art', 'Biology'...etc)

 

[phinix]

Create static fields for all subjects in the crosstab query. That is,

PIVOT [FieldName] In ('Art', 'Biology'...etc)

FANTASTIC !!!

Thank you so much for help! Making fixed column names fixed the problem!

I knew you're not gonna let me down
Thanks again!

 

[vbaInet]

You're welcome.

Glad we could help