Calculate frequency of answers occurring in fields (1 Viewer)

[NickDenyer]

Hi all,

...maths never was my strong-point...

So, I'd like to populate some fields with a count of the amount of times a specific answer occurs in a range of fields.

For example, I have 5 textboxes on a form, with possible answers A,B,C,D:

TB1 = A; TB2 = B, TB3 = A; TB4 = C; TB5 = A

So, I'd like to see in another set of Text boxes (called TotalBoxA-D):

TotalBoxA = 3, TotalBoxB = 1, TotalBoxC = 1; TotalBoxD = 0

Because A appeared 3 times, B and C both occured once, and D never appeared so it was listed as 0.

Is this possible? I'm sure it is, I just don't know how to do it: I'm a dreamer, but I'm not the only one...

Any help would be greatly appreciated!

THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[plog]

Just to be clear you have a form with 5 text boxes that are named "TB1" - "TB5". Also on this form are 5 text boxes named "TotalBoxA" - "TotalBoxD". What you should do is create a function in a module with this code:

Function getValueTotals(Target, V1, V2, V3, V4, V5)
    ' compares 5 values to a target value to see how many match
ret = 0
    ' value to be returned
VArray = Array(V1, V2, V3, V4, V5)
    ' puts values into array for easy looping
For i = 0 To 4
    ' loops through array to find matches
    If Target = VArray(i) Then ret = ret + 1
    Next i
getValueTotals = ret
End Function

Now on your form, set the control value of your Total boxes to this:

=getValueTotals("A",[TB1],[TB2],[TB3],[TB4],[TB5])


Being sure to replace the first argument (the "A" in the previous example) with the letter you are checking for in that box.

 

[NickDenyer]

Hi plog,

This worked exactly the way I was hoping thanks so much! I'm beaming with joy!

Thanks again,

N