IIF function and Dcount function

[Kaylen]

I have a table with fields headings in
numbers. The data in the fields is Y, N, or N/A. I want to calculate the
percentage of the count of Y over the count of Y and N, exclude N/A in the
denominator. But first I needed to filter out a certain selection in the
table only. That is why I made a query called "UM"( to filter out records
entered on certain date only). Now I want to calculate the number of Y/(N+Y)
based on data filtered by the query. I learned that you can't calculate if
the query has a parameter, or could you? The formula I use to calulate the Y
and N of field 13 in the query UM is

=DCount("[UM]![13]","UM","[UM]![13]='Y'")/(DCount("
[UM]![13]","UM","[UM]![13]='Y'")+DCount(" [UM]![13]","UM","[UM]![13]='N'"))

which works fine until all the data is if that field is "N/A", neither "Y'
or "N". Now I need a fomula that returns "N/A" if this is the case instead of
returning an error message. Please please help......

 

[johnies]

You can write a VBA routine that will do the calculation for you. On the "On Print" event in the section that you want the percentage to appear, open a recordset that corresponds to what you have in the grouping level. Then loop through this counting the "Yes" and "No" answers and calculate your percentage based on that. For example, place an unbound textbox in the group footer section in question. We'll call it txtPct. Then use this code

Dim strSQL as string
Dim intYes as integer
Dim intNo as integer
Dim sngPct as single
Dim rs as adodb.recordset
Dim cnn as adodb.connection

'Create the Sequel statement for the recordset
strSQL = "SELECT * FROM table or query name WHERE your criteria to match the grouping"
Set rs = new adodb.recordset
Set cnn = application.currentproject.connection

rs.Open strSQL,cnn,adOpenDynamic, adLockOptimistic

'Loop through the recordset counting the Yes and No answers.
While Not rs.EOF
If rs.fields("fieldname with the Yes or No value") = "Yes" then
intYes = intYes + 1
End if

If rs.Fields("fieldname with the Yes or No value") = "No" then
intNo = intNo + 1
End if

rs.movenext
Wend

rs.close

'calculate value to put n textbox Assuming you want the pct of Yes answers
Me.txtPct = intYes/(intYes + intNo)

set rs = nothing
set cnn = nothing

If you have questions or specifics from your db let me know.