Newby trying to total all the Yes's

[garprob]

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!

[#2]

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

[#3]

or simply use:

=Sum([Approval] * -1)

[#4]

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

[#5]

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

[#6]

it seems your Approval field is Short Text:

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