Counting occurrence of a letter

[vengsiva]

I have a table tbllab1 with the following fields

Labid :autonumber
Hospital Number :Text
Testdate :date
TestResult :Text
I want to return the count of "R" in the string TestResult.I designed the following query
SELECT tblLab1.LabId, tblLab1.[Hospital Number], tblLab1.Testdate, tblLab1.TestResult, IIf(Mid([testresult],1,1)="R",1,0)+IIf(Mid([testresult],2,1)="R",1,0)+IIf(Mid([testresult],3,1)="R",1,0)+IIf(Mid([testresult],4,1)="R",1,0)+IIf(Mid([testresult],5,1)="R",1,0) AS resdrugcount
FROM tblLab1
ORDER BY IIf(Mid([testresult],1,1)="R",1,0)+IIf(Mid([testresult],2,1)="R",1,0)+IIf(Mid([testresult],3,1)="R",1,0)+IIf(Mid([testresult],4,1)="R",1,0)+IIf(Mid([testresult],5,1)="R",1,0);
It works alright.Using a number of IIF functions looks awkward and will not work if testresult has more than 5 letters.Is there a better method?
Sivaraman

 

[Jon K]

You can create a public function in a Module to count the number of R's:-

Public Function countRs(sTestResult As String) As Integer
   Dim Num As Integer
   
   Do While InStr(sTestResult, "R")
     Num = Num + 1
     sTestResult = Mid(sTestResult, InStr(sTestResult, "R") + 1)
   Loop
   countRs = Num
   
End Function

and use the function in a query:-

SELECT Labid, [Hospital Number], Testdate, TestResult, countRs([TestResult]) AS resdrugcount
FROM tblLab1
ORDER BY countRs([TestResult]);
.

 

[vengsiva]

Thanks.It works fine!