Array

[Howlsta]

I have built a query including [Q1],[Q2] ....[Q6]. Each of these columns represents a question on an exam paper. I want to add the 4 highest question scores for each tuple together ignoring the 2 lowest. The sum will go into a total column.
I need to build an array in a module that gets rid of the lowest 2 marks, adds the other 4 together and passes the result to the total column in the query.
Can anyone help, please?

 

[D-Fresh]

Not the most efficient code below... But it will work for you... This just sorts the array and then takes the final four values...

Public Function GetSumHighest(quiz1 As Integer, quiz2 As Integer, quiz3 As Integer, quiz4 As Integer, _
    quiz5 As Integer, quiz6 As Integer) As Integer

    Dim arQuiz(6) As Integer
    Dim i As Integer
    Dim j As Integer
    Dim temp As Integer
    Dim intSum As Integer
    
    arQuiz(1) = quiz1
    arQuiz(2) = quiz2
    arQuiz(3) = quiz3
    arQuiz(4) = quiz4
    arQuiz(5) = quiz5
    arQuiz(6) = quiz6
    
    For i = 1 To 6
        For j = 1 To 5
            If arQuiz(j) > arQuiz(j + 1) Then
                temp = arQuiz(j)
                arQuiz(j) = arQuiz(j + 1)
                arQuiz(j + 1) = temp
            End If
        Next
    Next
        
    For i = 3 To 6
        intSum = intSum + arQuiz(i)
    Next
    
    GetSumHighest = intSum
    
End Function

Hope this helps...

Doug

 

[Howlsta]

Thanks.
I'm trying to call the function into my query now. I have put this expression in the field compartment

Expr1: GetSumHighest([q1],[q2],[q3],[q4][q5],[q6])

i changed some variables to meet the field names, and the new code is:

Public Function GetSumHighest(q1 As Integer, q2 As Integer, q3 As _
Integer, q4 As Integer, q5 As Integer, q6 As Integer) As Integer
Dim arQuest(6) As Integer
Dim i As Integer
Dim j As Integer
Dim temp As Integer
Dim intsum As Integer

arQuest(1) = [q1]
arQuest(2) = [q2]
arQuest(3) = [q3]
arQuest(4) = [q4]
arQuest(5) = [q5]
arQuest(6) = [q6]

For i = 1 To 6
For j = 1 To 5
If arQuest(j) > arQuest(j + 1) Then
temp = arQuest(j)
arQuest(j) = arQuest(j + 1)
arQuest(j + 1) = temp
End If
Next
Next

For i = 3 To 6
intsum = intsum + arQuest(i)
Next

GetSumHighest = intsum

End Function

but when I run the query i get #error in each compartment call me a retard, but what am i doing wrong now?

 

[D-Fresh]

Two things that stick out... First off, I think you need the Equals(=) sign in front of the function...

Expr1: =GetSumHighest([q1],[q2],[q3],[q4][q5],[q6])

And also, when you set the variables in the function, you don't need the brackets([])

So just use...

arQuest(1) = q1
arQuest(2) = q2
.
.
.

and so on... Hope this solves it for you.

Doug

 

[Howlsta]

Cheers, it wasn't working simply because some of the columns have null values in them. How can i get around this apart from telling the person entering info in the form to type zero's in null fields?

 

[Howlsta]

No worries, i've sorted it using the NZ function! Thanx