Yes/No Field

[Denver]

can anyone help me how to count the number of YES from my Yes/No Field in my
parameter query, is this possible?

I have my SQL here where can I put my criteria so that it displays the
number of YES from my YES/No Field..

SELECT [Data Entry].Location AS [Site Location], [Data Entry].[Drwg Typ] AS
[Drawing Type], [Data Entry].[Drawing Title], Count([Data Entry].[Drwg Typ])
AS [Total Records], Sum([Data Entry].[Qty of Sheets]) AS [Total Documents],
Sum([Data Entry].Eqv_A_Size) AS [Eqvlnt A Size], Sum([Data Entry].[Earned
ASize]) AS [EEqvlnt A Size], Avg([Data Entry].[Physical Prog]) AS [Physical
Progress], [Data Entry].[Rd Line] AS [Red Line], [Data Entry].AsBuilt AS [As
Built]
FROM [Data Entry]
GROUP BY [Data Entry].Location, [Data Entry].[Drwg Typ], [Data
Entry].[Drawing Title], [Data Entry].[Rd Line], [Data Entry].AsBuilt
HAVING ((([Data Entry].[Drwg Typ])=[Enter Drawing Type1] Or ([Data
Entry].[Drwg Typ])=[Enter Drawing Type2] Or (([Data Entry].[Drwg Typ])=[Enter
Drawing Type3] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type4]) Or ([Data
Entry].[Drwg Typ])=[Enter Drawing Type5] Or ([Data Entry].[Drwg Typ])=[Enter
Drawing Type6] Or (([Data Entry].[Drwg Typ])=[Enter Drawing Type7] Or ([Data
Entry].[Drwg Typ])=[Enter Drawing Type8] Or ([Data Entry].[Drwg Typ])=[Enter
Drawing Type9] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type10] Or ([Data
Entry].[Drwg Typ])=[Enter Drawing Type11]) Or (([Data Entry].[Drwg
Typ])=[Enter DrawingType12] Or ([Data Entry].[Drwg Typ])=[Enter Drawing
Type13] Or ([Data Entry].[Drwg Typ])=[Enter Drawing Type14] Or ([Data
Entry].[Drwg Typ])=[Enter Drawing Type15] Or ([Data Entry].[Drwg Typ])=[Enter
Drawing Type16])));

any help will be much appreciated
thanks

denver

 

[John Spencer]

Use a formula

Abs(SUM([YesNoField])) as CountOfYes

OR
Count(IIF([YesNoField]=True,1,Null))

The difficulty is that Count returns a count of the non-null values. A Yes/no
field always has a value of True (-1 in Access) or False (0). That being the
case you end up with a count of rows.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Denver wrote:

 

[Denver]

thanks it really works, i just make some modify but it really works..
thanks again