Ave, Min and Max in Report

[Chicagodude]

I have the five text box on my report.
Textbox1 - Result_1 - Feild name in database table
Textbox2 - Result_2 - Feild name in database table
Textbox3 - Result_3 - Feild name in database table
Textbox4 - Result_4 - Feild name in database table
Textbox5 - Result_5 - Feild name in database table

I have three more text box below this five text box.
TextboxMin
TextboxMax
TextboxAvg

I want a autofill up these three text box based on the first five text box value.
I need Max value out of first five text box
Min valude out of first five text box and Ave out of first five text box.

Sometime one of the text box out of 5 is empty. How can i achive this in Report.

 

[MajP]

Are these five different fields, or five records? It looks like 5 different fields, but want to make sure.

 

[Chicagodude]

Five different feilds.

 

[MajP]

you can use functions like these in a query or calculated control
Just pass in your fields or control values.

Public Function getMax(ParamArray aVals() As Variant) As Variant
  Dim varVal As Variant
  For Each varVal In aVals
    If IsNull(varVal) Then
      Exit For
    ElseIf IsNull(getMax) Then
      getMax = varVal
    ElseIf getMax < varVal Then
      getMax = varVal
    End If
  Next varVal
End Function

Public Function getMin(ParamArray aVals() As Variant) As Variant
  Dim varVal As Variant
  For Each varVal In aVals
    If IsNull(varVal) Then
      Exit For
    ElseIf IsNull(getMin) Then
      getMin = varVal
    ElseIf getMin > varVal Then
      getMin = varVal
    End If
  Next varVal
End Function

Public Function getAve(ParamArray aVals() As Variant) As Variant
  Dim varVal As Variant
  Dim intCount As Integer
  Dim sngTotal As Single
  For Each varVal In aVals
    If IsNull(varVal) Then
      Exit For
    Else
      intCount = intCount + 1
      sngTotal = sngTotal + varVal
    End If
  Next varVal
  If intCount > 0 Then
    getAve = sngTotal / intCount
  End If
End Function

 

[fneily]

Do you realize your table is not normalized?
Result_1 - Feild name in database table
Result_2 - Feild name in database table
Result_3 - Feild name in database table
Result_4 - Feild name in database table
Result_5

Not correct table design.

 

[Chicagodude]

Thanks it works. I need to do minor adjustment on GetMin function.