Count Fields Within A Record

[ndeans]

I'm building a health data system that stores data from health tests as follows:
[test01], [test02], [test03],.........[test25], etc etc.
Each of these tests comprised of a "Pass"/"Fail" value.

Now, i'm building my queries for a feedback report to hand over to the client that is based solely on the 1 single client record. In other words the queries are designed to return only 1 single data record to be placed in the client report where the based on the corresponding [TestID].

So within this 1 data record i want a query to count the number of times "Pass" appears in the test fields?

ie count([test01] [test02] [test03] [etc] [etc], ="Pass")

As there are some 40-50 [test##] fields in this one test battery i don'y particularly want to go back and code an IIf statement for each component if i can help it.

Many thanks in advance for any tips.

 

[KeithG]

You will have to create a custom function You can use something like below. This function will search a string and return how many times a value is present in the string.

Public Function CountString(Text As String, SearchFor As String) As Integer

    Dim strText, strSearch As String
    
    strText = Text
    strSearch = SearchFor
    
    X = 1
    
    
    Do
        If Mid(strText, X, 4) = strSearch Then
            intCount = intCount + 1
        End If
        
        X = X + 1
    
    Loop Until Mid(strText, X, 4) = ""
    
    CountString = intCount
    
         

End Function

Is this what you are trying to accomplish

 

[Pauldohert]

Are the results for Test1 test2 etc held in the tables as separarte records linked to the organisation. If these are not held like this maybe they should be.


If so you could use a Dcount to count the passes.

 

[ndeans]

My Solution

I've settled on the following solution. Cheers for your input.

SPass: Sum(IIf(

![test01]="Pass",1,0)+IIf(

![test02]="Pass",1,0))

It actually serves my purpose to split the report into sections anyway so i'll do a final total on the report rather than the query.

Cheers All