Forum Discussion

Copper Contributor

Jan 31, 2023

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 repor...

access

Karl_Donaubauer

MVP

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

garprob

Copper Contributor

Feb 02, 2023

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?

arnel_gp
Steel Contributor
Feb 03, 2023
it seems your Approval field is Short Text:

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