Percentage in a Text Column

[ScottBeatty]

Hello all, I have about 20 columns in a query and each row is a customer.
The columns are either pass/fail but are text fields and not a yes/no set up. I want to get the percentage of a pass in the column. Can somebody help me?

Thanks!

-Scott

 

[billmeye]

If I understand, you would like to add up all the passes in a row and divide by the number of Pass/Fail columns you have? You could use a Function that could do the counting but only if you have some way to identify all the Pass/Fail columns or you can ensure a fixed number and the column will always contain either the word Pass or Fail.

Here is a function that would count the number of passes and it assumes that every Pass/Fail column has the words Pass or Fail as a value. To use it you need to provide the name of the table, the name of the primary key and the primary key value.

Function CountPass (tblName as string, PKName as String, PKID as Long) as Integer
Dim db As DAO.Database 
Dim rs as DAO.Recordset
Dim fldCnt as Integer
Dim MySql as String

Set db = CurrentDb 
MySql = "SELECT * FROM " & tblName & " WHERE (((" & tblName & "." & PKName & ")=" & PKID & "));"
Set rs = db.OpenRecordset(MySql,dbOpenSnapShot)
rs.MoveFirst
Do While Not rs.EOF

   ' Loop through all the fields (columns) 
   For fldCnt = 0 to rs.Fields.Count - 1
     If rs.fields(fldCnt).Value = "Pass" or rs.Fields(fldCnt).Value = "Fail" Then
	If rs.fields(fldCnt).Value = "Pass" Then CountPass = CountPass + 1
     End If
  Next fldCnt
rs.MoveNext
Loop 

rs.close
db.close
Set rs = nothing
Set db = nothing
End Function

 

[ScottBeatty]

I actually want to add up all of the passes in a column. This way I can use a filter to single out a single customer and see the percentage by customer. I've been trying to use the criteria in design view to count the passes and that works and everything but I cannot figure out a way to get percentages.

I've been entering in "PASS" and that works fine to get a numeric count, but I just do not know how to get a percentage from that.

 

[billmeye]

You would need the total number of records and the total number of records that contain Pass. It may be easier to use VBA and the DCount function. For example:

PassPerCol2 = FormatPercent(Dcount("*","tblYourTable","[column2]='Pass' And [PersonID] =" & Me.PersonID) / DCount("*","tblYourTable", [PersonID] =" & Me.PersonID))

 

[ScottBeatty]

Sorry but I'm not crazy code savvy :banghead:, and I got an error message reading "The expression you entered contains invalid syntax. You may have entered an operand without an operator."

My exact code is as follows:

PassPerCol2= FormatPercent(DCount("*","Headings From Client","[Final Payoff]"= 'PASS' And [Client]=" & Me.Client/Dcount("*","Headings From Vision", [Client]=" & Me.Client))​

My Table name is= Headings From Client
Column name= Final Payoff where the results are either Pass or Fail. Wanting the percentage of passes in that column

Another question that may be stupid (again, I'm a rookie), but is builder the same thing as VBA? When I'm in query design, I went up to builder and starting typing away and what I typed in entered into the criteria for the column.

 

[billmeye]

I was thinking VBA but your in query builder. VBA is used behind forms an reports and modules but not queries.

Try this:

If Client is a number:

PassPerCol2= FormatPercent(DCount("*","Headings From Client","[Final Payoff]= 'PASS' And [Client]=" & [Client])/Dcount("*","Headings From Vision", [Client]=" & [Client]))

or if it is text:

PassPerCol2= FormatPercent(DCount("*","Headings From Client","[Final Payoff]= 'PASS' And [Client]='" & [Client] & "'")/Dcount("*","Headings From Vision", [Client]='" & [Client] & "'"))

 

[ScottBeatty]

Client is a text. I got another, but different, error message reading "The expression you entered has an invalid string. A string can be up to 2048 characters long, including opening and closing quotation marks."

Code:

PassPerCol2= FormatPercent(DCount("*","Headings From Client","[Final Payoff]= 'PASS' And [Client]='" & [Client] & "'")/Dcount("*","Headings From Client", [Client]='" & [Client] & "'"))

 

[billmeye]

Scott, would you like to attach your DB? I can better troubleshoot it.

 

[ScottBeatty]

I really wish I could, but it contains confidential information.

 

[billmeye]

This error:

"The expression you entered has an invalid string. A string can be up to 2048 characters long, including opening and closing quotation marks."

usually pops up when there is a syntax error or reference typed incorrectly. A missing/extra quote, wrongly syntaxed reference. That's why I wanted to see your query. Could you just give me a blank table and the query?