Numbers separated by comma in text box

[mdg]

I was wondering if there is simple way to do this. I have a single 'text' box on a form that captures test scores. The person inputing the test scores puts in all of the scores separated by commas (77, 85, 100, 75.5).. Up to 6 test scores can be placed in this single text box. The field is formated for text. What I propose to do is take the numbers in the text box and average them out and then display the test scores average in a number field named 'avescores' next to this field in the form. Once the inputer puts the series of scores into the text box, they would hit an 'update' button next to this box to 'update' the 'avescores' box. Right now they use a number pad and add up the numbers manually then place the average into the avescores box....THanks in advance

 

[pbaldy]

One way that comes to mind would use the Split() function. You'd add up the values within the customary loop of the values, and you can derive the denominator from the UBound value. More info in VBA help.

 

[mdg]

One way that comes to mind would use the Split() function. You'd add up the values within the customary loop of the values, and you can derive the denominator from the UBound value. More info in VBA help.

Thank you for your reply. I will give that a try.

 

[pbaldy]

No problem; post back if you get stuck.

 

[mdg]

No problem; post back if you get stuck.

I am sort of stuck. What I have is the following.
1. Form: frmMain
2. tlb: tblEnrollment
3. [ExamScores] formatted for text - input data as follows: 72, 100, 88 etc... Could have as few as one exam score entry and as many as 6 exam scores, all separated by a comma.
4. [AverageScore] nuimber format - averages the scores in the [ExamScores] field.

What I want to be able to do is once the exam scores are entered, the averagescore box is auto filled with the average of all the exam scores entered. So if 2 exams scores are entered, they would be added together and then divided by 2 and so forth. The update could be accomplished when the form refresh button is pressed.
What do you think? I've incorporated several of your codes from you web site in my database and they work really well.

 

[pbaldy]

Where exactly are you stuck? Have you tried the Split() function like I mentioned? Personally I'd probably create a public function that accepted the delimited string as an input and returned the average, but that function would use the Split() function. I'd create the public function so I could call it from anywhere.

 

[mdg]

I haven't been able to find any information on using the split function in the manner I mentioned. I am not that familiar with it either..

 

[pbaldy]

Along these lines:

Public Function GetAverage(varInput As Variant) As Double
  Dim astrValues()            As String
  Dim L                       As Long
  Dim dblAmount               As Double

  astrValues = Split(varInput, ",")

  For L = 0 To UBound(astrValues)
    dblAmount = dblAmount + astrValues(L)
  Next L

  GetAverage = dblAmount / (UBound(astrValues) + 1)
End Function

 

[mdg]

Along these lines:

Public Function GetAverage(varInput As Variant) As Double
  Dim astrValues()            As String
  Dim L                       As Long
  Dim dblAmount               As Double
 
  astrValues = Split(varInput, ",")
 
  For L = 0 To UBound(astrValues)
    dblAmount = dblAmount + astrValues(L)
  Next L
 
  GetAverage = dblAmount / (UBound(astrValues) + 1)
End Function

Thank you for the code. I will apply it and see if it works and let you know. I was playing around with something similar but could not make it work. Is the +1 because there is no comma after the last exam number?

Am working on another database at the moment and will get to this one later on today.

 

[pbaldy]

Is the +1 because there is no comma after the last exam number?

No, it's because arrays are zero based. A test with 4 values in it would loop from 0 to 3.

 

[mdg]

No, it's because arrays are zero based. A test with 4 values in it would loop from 0 to 3.

That code worked great!! Does exactly what I wanted. Thanks so much for your help. You helped make my project better than it was...

 

[pbaldy]

Happy to help!