Forum Discussion
Newby trying to total all the Yes's
HI. I am not an Access user and know very little about it. I do use one Access 2007 database as one part of my job. I now need to create a report from it. I have figured out how to make the report except for one little detail needed to finish it up. This is the scenario- this report is made up of six fields. One of the fields is "Type". I have grouped the report by the "Type" field and have subtotals at the end of each group and a grand total at the end of the report. I have another field, "Approval", with Yes or No as the only options in that field. The report is not grouped by yes or no in this field. I need to have the report calculate the number of Yes's throughout the entire report and put that total down at the bottom of the report. Is there a way I can accomplish this? Any help will need to be pretty step by step. Thanks!
Hi,
Insert a new text box in the section where you want to see the sum (e.g. in the report footer) and use this expression in the property ControlSource of the text box:
=Abs(Sum([Approval]))
In Access Yes is saved as -1, No = 0. Therefore the expression just sums up the values and makes the sum positive with the Abs() function.
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon- or simply use:
=Sum([Approval] * -1)Hi Arnel,
1. I would only ever convert the sum once, not each individual number.
2. I sometimes use the even simpler -Sum(xyz) in projects where I'm sure it will remain in Access completely and forever. In public forums, I got out of the habit of giving this "-" advice, so that when people (OPs or readers) will eventually use another backend like SQL Server, things will still work for them. Hence the universal Abs().
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevConThanks for your ideas, but when I type in either of those formulas I get an error- "Data type mismatch in criteria expression". I included a couple of screen shots showing what a portion of the report and the design screens look like. I tried putting your formula in the text box next to "total approved" where it currently says "unbound". Have I done something wrong?
