Question Issues with long GPA calculation

[dmschenk]

I am a bit of Access newbie and am taking over a report card system for a church school.
I am having an issue with the pre-existing database for creating report cards. I am trying to add a GPA calculation however I am running into length of calculation issues. There are 48 classes in a course and when I try to add them to the query it tells me right off "The expression you have entered is too complex."
I can only get 30 of the classes into the calculation before it fails. I'm not sure how to make this less complex or to break it down into smaller chunks.

Any help would be wonderful
Thanks

Here is the current field calculation and the function

GPA: Val(RAvg(
[1a],[1b],[1c],[2a],[2b],[2c],[3a],[3b],[3c],[4a],[4b],[4c],
[5a],[5b],[5c],[6a],[6b],[6c],[7a],[7b],[7c],[8a],[8b],[8c],
[9a],[9b],[9c],[10a],[10b],[10c],[11a],[11b],[11c],[12a],[12b],[12c],
[13a],[13b],[13c],[14a],[14b],[14c],[15a],[15b],[15c],[16a],[16b],[16c]))


Function RAvg(ParamArray FieldValues()) As Variant
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

 

[Taruz]

You do to trick..


GPA: (
Val(RAvg(
[1a],[1b],[1c],[2a],[2b],[2c],[3a],[3b],[3c],[4a],[4b],[4c],
[5a],[5b],[5c],[6a],[6b],[6c],[7a],[7b],[7c],[8a],[8b],[8c]
))
+
Val(RAvg(
[9a],[9b],[9c],[10a],[10b],[10c],[11a],[11b],[11c],[12a],[12b],[12c],
[13a],[13b],[13c],[14a],[14b],[14c],[15a],[15b],[15c],[16a],[16b],[16c]
))
)
/2

 

[dmschenk]

Wow that was simple!! Thanks so much for your quick response!!!

 

[dmschenk]

It looks like I was mistaken... if either the first or second "Val" is empty then the calculation fails. There is a good chance that this combination can happen.

 

[Taruz]

Hi again..

Replace this section..:

....
......
Else
'RAvg = Null
RAvg = 0
End If
......
..............

and this..:

GPA: (
Val(RAvg(
[1a],[1b],[1c],[2a],[2b],[2c],[3a],[3b],[3c],[4a],[4b],[4c],
[5a],[5b],[5c],[6a],[6b],[6c],[7a],[7b],[7c],[8a],[8b],[8c]
))
+
Val(RAvg(
[9a],[9b],[9c],[10a],[10b],[10c],[11a],[11b],[11c],[12a],[12b],[12c],
[13a],[13b],[13c],[14a],[14b],[14c],[15a],[15b],[15c],[16a],[16b],[16c]
))
)
/
iif(Val(RAvg(
[9a],[9b],[9c],[10a],[10b],[10c],[11a],[11b],[11c],[12a],[12b],[12c],
[13a],[13b],[13c],[14a],[14b],[14c],[15a],[15b],[15c],[16a],[16b],[16c]
))=0,1,2)

 

[dmschenk]

I'm not sure what I'm doing wrong here but the numbers don't average out correctly all the time.
There are basically two "sets" of values.
When I add numbers to the "first" set of values the average is correct.
When I add numbers to the "first and second" set of values the average works correctly when the numbers are evenly divided. in other words 6 numbers in each set.
When I add numbers to "only the second" set of values the average is half what it should be.