I have a problem that I need solved and am willing to pay for the solution. I have a report that has a bunch of check boxes from a table and I need to add all of the checked boxes in each column and get a total at the bottom. I am pretty sure that I need to use the count() or dcount() functions to make this happen, but every time I try it, I get a da#ned Error in the data field. I can pay up to $40 per hour for an answer.
Counting Check Boxes This particular aspect of Microsoft Access had me puzzled for some time. How do you count Check Boxes (Yes/No data types)? – Have any of you ever tried this. A result is recorded for all check boxes whether they be positive or negative, Yes or No, checked or unchecked. The values that are stored are -1 for Yes and 0 for No. The problem therefore occurs when you wish to count how many Yes answers there are and/or how many No’s, as both answers are counted. If you used the Count function, the total count of all the records would be the result – as there is a value recorded for this data type in all records. You can, of course, do it in a few queries by filtering the entire Yes answers and counting them and then all the No’s and counting them, but it is a bit messy – and does not solve the problem of counting them in a form or a report. One way to get around this is to use the Sum function instead. This works well for Yes answers as it will add all the -1 resulting, in the number of Yes answers, albeit as a –ve number. To change into a positive number, multiply the result by –1, when required. This does not solve the problem for No answers. For this you would have to count all the records (check boxes) and add the Sum of Yes’s – remember the Sum of Yes’s are –ve, therefore this is the same as subtracting a positive number. The formulas would look like this (just change the ‘NameOfCheckBox’ to the name of your check box): Yes (checked): Sum(NameOfCheckBox)*-1 No (not checked): Count(NameOfCheckBox)+Sum(NameOfCheckBox) If you are doing this in a form or a report, you will need to add an = sign before the formulas. It works best if you add this to the report footer or header. I hope I have explained this well enough. That took me 4 hours.....please make the checks payable to me please but i would prefer cash
Andy -- First, make sure the text box which you are using to calculate the total is located in the Report Footer section; otherwise it will return #err. I wasn't sure if you were calculating a total based on whether a field was checked or not or counting checkboxed so here are two solutions: 1) To sum a series of records based on your set criteria, use the function DSum = ("Field to be calculated", "Table or query name", "criterion: in this case CheckboxField = True"). Be sure each element in the function is housed in quotations. 2) To count a series of records based on set criterion, use the function DCOUT, which follows the same syntax as DSum. Again, be sure these calculated Text Boxes are placed in the Report Footer and not the page footer. I hope this helped.
I had this exact same problem a while back and it took a while to figure out ... If the other guys explanations are not enough, let me know and I can try to dig my old work project out and see if I can remember what I did.
