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
Numbers separated by comma in text box
- Thread starter mdg
- Start date
- Local time
- Today, 06:15
- Joined
- Aug 30, 2003
- Messages
- 36,256
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.
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.
- Local time
- Today, 06:15
- Joined
- Aug 30, 2003
- Messages
- 36,256
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.
- Local time
- Today, 06:15
- Joined
- Aug 30, 2003
- Messages
- 36,256
Along these lines:
Code:
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 FunctionThank 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.
That code worked great!! Does exactly what I wanted. Thanks so much for your help. You helped make my project better than it was...
Similar threads
Solved
Column in Text Box
