Control Source Syntax?

[osmondb]

Hi, I have a report where I have added an unbound text box.
In the text box I want to count the total number of 'Yes'
values for records that match certain criteria. For
example, let's say I have a table like this:

Field1 | Field2 |
--------------------
A | Yes |
B | Yes |
A | No |
B | No |
A | Yes |
A | No |

What I want is to count the total number of 'Yes' in Field2
for every A value in Field1. I've tried creating a query
that returns the records in Field1 that are equal to A,
then I added an Expr1 to the query that was
=Abs(Sum([Field2])), assuming that I could then link Expr1
to the report that I created but I was getting the error
"You have chosen fields for the record that the wizard
can't connect. You may have chosen records from a table and
from a query based on that table (which I did)". I then
tried to change the control source of a box on the report I
added to say
=Abs(Sum([Field2])) WHERE [Field1]="A"
but I received errors for this as well. I'm not sure where
to go to from here. As I have little to no knowledge of SQL
I'm sure there's an error with the above statement. Any
help/comments would be greatly appreciated!

 

[Newbie]

Try something like:

SELECT Table1.Field1, Count(Table1.Field2) AS CountOfField2
FROM Table1
where Field1 = "A" and Field2 = Yes
GROUP BY Table1.Verse

HTH

 

[John Spencer (MVP)]

Try putting the expression in the control source of the new control and putting
the control itself in a group footer.

=Abs(Sum([Field2]=True))

If you want ONLY A that = Yes

=Abs(Sum([Field1]="A" AND [field2]=True))

 

[osmond]

-----Original Message-----
Try putting the expression in the control source of the new control and putting
the control itself in a group footer.

=Abs(Sum([Field2]=True))

If you want ONLY A that = Yes

=Abs(Sum([Field1]="A" AND [field2]=True))

Thanks for the help! That worked perfectly.