By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,515 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,515 IT Pros & Developers. It's quick & easy.

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

P: 20
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("FIMInaccurate","Period Query","FIMInaccurate=Yes")

=(DCount("FIMInaccurate","Period Query","FIMInaccurate = yes"))/(DCount("FIMInaccurate","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.
Jul 19 '10 #1

✓ answered by 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

Share this Question
Share on Google+
7 Replies


mseo
100+
P: 181
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
Jul 19 '10 #2

P: 20
@mseo
Thank you for the option you provided. How do I then obtain the percentages using this approach?
Jul 19 '10 #3

mseo
100+
P: 181
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
Jul 19 '10 #4

P: 20
@mseo
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.
Jul 19 '10 #5

mseo
100+
P: 181
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
Jul 19 '10 #6

P: 20
Thanks for the help. This worked out very well.
Jul 20 '10 #7

mseo
100+
P: 181
@cambar
you are welcome cambar
Welcome to bytes again
glad we could help
Jul 20 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.