Can't get a report total and % to work when dates are made variable in query

[cambar]

Created a report in Access where each record has two checkboxes "FIM Incomplete" and "FIMInaccurate" . Have been able to create totals and percentages at bottom of report for each. However, once I alter the query to require the user to enter a start date and end date for the period of time the information is desired, the expressions at the bottom of the report result in "error". What needs correction to allow for this variable? Currently, the expressions are:

=DCount("FIMIna ccurate","Perio d Query","FIMInac curate=Yes")

=(DCount("FIMIn accurate","Peri od Query","FIMInac curate = yes"))/(DCount("FIMIna ccurate","Sr Metrix Period Query"))

I'm not a programmer (wish I was and knew a whole lot more about syntax) so any help would be very appreciated.

[mseo]

hi, cambar
Welcome to bytes
you can post your sql of this report in here
and if you want to count the checked checkboxes in your report you can do simple way put text box in the group footer and put something like this
=Sum([your checkbox control name])*-1
this way you can count the checked checkboxes

p.s the startdate and enddate are parameters in queries and parameters are Parallel Variable in VBA code
you can add checkbox parameter as well in whatever, your form or report based on query but this will be a bit different if you do this within a form
hope this helps

[cambar]

hi, cambar
Welcome to bytes
you can post your sql of this report in here
and if you want to count the checked checkboxes in your report you can do simple way put text box in the group footer and put something like this
=Sum([your checkbox control name])*-1
this way you can count the checked checkboxes

p.s the startdate and enddate are parameters in queries and parameters are Parallel Variable in VBA code
you can add checkbox parameter as well in whatever, your form or report based on query but this will be a bit different if you do this within a form
hope this helps

Thank you for the option you provided. How do I then obtain the percentages using this approach?

[mseo]

please post your sql statement of your report and if you can attach snapshot of your report that would help us understand your problem properly

[cambar]

please post your sql statement of your report and if you can attach snapshot of your report that would help us understand your problem properly

For proprietary reasons, cannot post copy of report. Let me try one more time to explain. The query I developed for the report in question has a date field. In this date field, the criteria requires the user to input the time period (dates) for which the report information is desired Example: Between[Enter Start Date:]And [Enter End Date:] When the report prints out, I would like to be able to total two of the columns which are checkboxes. Your response for summing these columns worked great. However, I also need to determine the percentage of -1 responses as compared against total column responses. The expression I initially posted which was placed in the report footer provided this information as long as the date field criteria was left blank or was filled with an actual date in the query. When it was filled with the criteria "Between... ." the expression in the report would no longer work. Since your expression worked well in providing the addition of the checkboxes, I am only seeking to know how I can adjust it to provide the percentage. Thank you for your help.

[mseo]

ok,
if I understand your question correctly, you want to view the percentage of the checked checkboxes to the total of records, if that right
you can put textbox and set it invisible to count all the checkboxes using =Count([checkbox field name])
then put textbox to view the percentage and set its format to percent = NZ((name of text box that count the checked checkboxes)/(name of textbox that counts all the checkbox),0)

if it doesn't work as expected, let me know
hope this helps

[cambar]

Thanks for the help. This worked out very well.

[mseo]

Thanks for the help. This worked out very well.

you are welcome cambar
Welcome to bytes again
glad we could help