Sum if Yes

[turtle]

I have a report that I need a field summed in the header if another
field = yes.

Hrs Yes/No
5 Y
10 N
8 Y
2 N

In the header I would like it to sum the hours where Yes/No field =
Yes.

so the total would be 13 in this example.
thanks,
KO

[jimfo...@compumarc.com]

After renaming Yes/No to AnotherField and calling the table tblHours:

SELECT Sum(Nz([Hrs])) AS theSum FROM tblHours WHERE AnotherField =
True;

gave theSum of 13. AnotherField is of type Yes/No.

James A. Fortune

[turtle]

I would like to do this on the report not in the query if that is
possible

[jimfo...@compumarc.com]

turtle wrote:
> I would like to do this on the report not in the query if that is
> possible

In the ControlSource for the textbox on the report:

=GetValue("SELECT Sum(Nz([Hrs])) AS theSum FROM tblHours WHERE
AnotherField = True;", "theSum")

Then in a module:

Public Function GetValue(strSQL As String, strField As String) As
Double
Dim MyDB As Database
Dim MyRS As Recordset

GetValue = 0
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount > 0 Then
MyRS.MoveFirst
GetValue = MyRS(strField)
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function

James A. Fortune

[fredg]

In an unbound text control:

=Sum(IIf([Yes/NoField]=-1,[Hrs],0))

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[MGFoster]

You might try this in a TextBox's ControlSource property:

=Sum(IIf([Yes/No]="Y", Hrs, 0))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[jimfo...@compumarc.com]

I tried not to assume that the report was based on tblHours, but if
that's the case then simpler is better.

James A. Fortune

[Trevor Best]

fredg wrote:
> On 7 Mar 2005 10:05:25 -0800, turtle wrote:
>
>
>>I have a report that I need a field summed in the header if another
>>field = yes.
>>
>>Hrs Yes/No
>>5 Y
>>10 N
>>8 Y
>>2 N
>>
>>In the header I would like it to sum the hours where Yes/No field =
>>Yes.
>>
>>so the total would be 13 in this example.
>>thanks,
>>KO
>
>
> In an unbound text control:
>
> =Sum(IIf([Yes/NoField]=-1,[Hrs],0))
>

or

=Sum(Abs([Yes/NoField])*[Hrs])

--
This sig left intentionally blank