Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Newby trying to total all the Yes's

Copper Contributor

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!

5 Replies

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 DevCon

@Karl Donaubauer 

Thanks 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?

garprob_1-1675376885061.png

 

 

garprob_0-1675376379448.png

it seems your Approval field is Short Text:

=Sum(([Approval] & "" = "Yes") * -1)