Gotcha, so open the Form "Report Generator". Using the "States" drop down select "TX" leave everything else blank. Click the button "Design Ans. Query". After the query runs a table and a report will open. The report that opens is "Design Report". In the blue header you will see totals Yes=3976, No=2, Points=153, WR's=Blank, Fail=Blank, Pass=Blank. Then below that you will see other information. When the query is run as stated the value 153 in the "Points" text box is accurate as is the value in the "Yes" and "No" text boxes the others are blank and hopefully the help I am requesting will fix those. If you were to rerun this same query only selecting "TX" for States and then selecting "2" for the "Design/Construction Answers" drop down on the form "Report Generator" and clicking "Design Ans. Query" button you will get values in the "Design Report" header of Yes=0, No=2, and Points=1. Here is my problem.
When running this or my other queries without additional filters the simple equation in the "Points" text box =IIf([DYes]+[DNo]/26) provides the correct value 153. But if you add the additional filter such as selecting "2" for the "Design/Construction Answers" this equation is no longer valid as you will get 0+2/26=.07 points. The current equation I have in the "Points" text box is =IIf([DYes]<>0,([DNo]+[DYes])/26,[DNo]/(Count([NoOfPoints]))) this equation again works when no additional filter is applied but provides an answer of "1" when the other filter is applied.(i.e. 2/2=1).
Go back to the form and change "States" to "LA" and leave "Design/Construction Answers" set at "2". This query will give you a better idea of my overall issue. The totals will be Yes=0, No=10, Points=1 and the rest blank. I feel I can get an equation to work properly if I attempt to reference another text box within this report.This is the equation I attempted to put in the "Points" text box when I began recieving the "Enter Parameter" issue. =IIf([DYes]<>0,([DNo]+[DYes])/26,Count([DPF])). The answer should be 7 but it wants me to "Enter a Parameter". If I were to use the equation =IIf([DYes]<>0,([DNo]+[DYes])/26,Count([NoOfPoints])) I don't have to "Enter a Parameter" but the answer is 10 as there is a point associated with each row of the query.
I am basically trying to get the "Points" to equal the true number of points that show up on the report regardless of the amount of rows that are produced from the query. In the property sheet of [NoOfPoints] I have duplicates hidden so I understand the program is doing what I am asking by counting the true number of points which is the quantity of rows produced but in this particular query each point could have from 1-26 rows depending on the data thus I need some equation that will allow me to take [NoOfPoints] and divide by the moving target 1-26.