Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Counting Yes/No Fields

Posted on 2000-03-21
Medium Priority
532 Views
I have a field that is defined Yes/No.  I want to put two calculated fields on a report and have one count for the yes and one count for the no.  I have tried this count([fulltime] = True) and it still returned the same count regardless.
0
Question by:Accessed
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 3
• +7

LVL 7

Expert Comment

ID: 2641205
It may depend on where you want the totals to show up.  I think I did something similar to this a long time ago...
If you want the yes/no counts to appear in the report footer, put a subreport in the footer.  The subreport is just based off a query that gets the totals for you.
I created a sample table with the following fields:
cntFieldID as AutoNumber
bYesNo as Yes/No

I put a bunch of records in the table, then created this query/SQL to give me counts:
SELECT Table1.bYesNo, Count(Table1.cntFieldID) AS CountOfcntFieldID FROM Table1 GROUP BY Table1.bYesNo;
0

LVL 4

Expert Comment

ID: 2641256
How bout this

I created a table
one of the fields is called IsOkay   data type Yes/no

On the report in the report footer I placed two unbound text boxs

The control source for these box's are as follows

For yes's =Sum(IIf([isokay]=-1,1,0))

For No's =Sum(IIF([IsOkay] = 0,1,0))

I think your count function using the = sign is going to give you the same answer because there is always something in a yes no field.  -1 = yes and 0 = no

Andy

0

LVL 7

Expert Comment

ID: 2641260
Hi Accessed.

1. Locate on detail section 2 invisible fields txtYes and txtNo, set for this fields "Control Source" property to:
=IIf([fldBoolean]=True;1;0)
=IIf([fldBoolean]=False;1;0)
respectively and "Running Sum" to "Over All".
2. Locate txtCountOfYes and txtCountOfNo in the report footer and set control sources to:
=[txtYes]
=[txtNo]
3. Save, Close and Run your report.

Regards,
Dedushka
0

LVL 10

Expert Comment

ID: 2641261
Hello Accessed,

here's another way I use quite often. You can play with other conditions like this too. Set your textfields Control Property like this:

Count Yes/True values:
=SUM(ABS([yes/no]=True))

Count No/False values:
=SUM(ABS([yes/no]=False))

Regards,
0

LVL 3

Expert Comment

ID: 2641436
Hi, Accessed, I kinda like the domain aggregate functions to do this, like below:

The "DCount(expression, domain, criteria)" will do what you want.  Make it the control source for the appropriate text box.  The arguments must be strings (enclosed in quotes), and, in the property sheet, you will need an equals sign (=) in front of the function.  The expression below will give you the True count (substitute your names for the generics I've used):

=DCount("[FieldName]","TableName","[FieldName] = True")

This next one will give you the False count:

=DCount("[FieldName]","TableName","[FieldName] = True")

Just another way...good luck as usual...
0

LVL 3

Expert Comment

ID: 2641452
Oops, sorry!  The second one should have "= False" not true (blush).  Look before you leap, right? :o)
good luck as usual...
0

LVL 31

Expert Comment

ID: 2641479
In your detail section create two unbound fields:

Name          : y
Control Source: =IIf([fulltime]=-1,1,0)
Running sum   : Over All
Visible       : No

Name          : n
Control Source: =IIf([fulltime]=-1,0,1)
Running sum   : Over All
Visible       : No

In report footer, create two unbound fields:

Name          : y_count
Control Source: =[y]

Name          : n_count
Control Source: =[n]

0

LVL 10

Expert Comment

ID: 2641501
hi tomk,

I think DCount() would okay - even it takes more resources - when it's used counting grand totals, but you need to change criterias each time when you count different break level summaries. Something like

=DCount("[FieldName]","TableName","[FieldName] = True and [break1_fieldname='" & [break1_fieldname] & "'")

=DCount("[FieldName]","TableName","[FieldName] = True and [break2_fieldname='" & [break2_fieldname] & "'")

....

Other disadvange is how to restrict the count if the user runs the report with different criterias.

However you can copy/paste =SUM(...) fields into different break sections and they work without modification.

Best regards,
0

LVL 10

Expert Comment

ID: 2641521
Another example how to calculate different counts:

=ABS(SUM([field1]="large"))
will count number of records in the report group having field1 value "large"

=ABS(SUM([field1]="medium"))
will count number of records in the report group having field1 value "medium"

=ABS(SUM([field1]="small"))
will count number of records in the report group having field1 value "small"

0

LVL 7

Expert Comment

ID: 2641580
hnasr,
0

LVL 7

Expert Comment

ID: 2641626
I *knew* hnasr's answer looked familiar!  (LOL)
0

LVL 3

Expert Comment

ID: 2641634
Hi, paasky, you make some good points, but if the domain for the DCount() is always the same as the record source for the report, then user criteria wouldn't matter as far as totals go.  I agree that you would have to set it up that way, but it doesn't sound too hard.  To get different breaks (grouping) would involve extended criteria as you say.  Thanks for the logic, the questioner always comes away with more than just an answer when we do this.  :)
good luck as usual...
0

LVL 10

Expert Comment

ID: 2641692
(Dedushka)
=IIf([fldBoolean]=True;1;0)
=IIf([fldBoolean]=False;1;0)

(hnasr)
=IIf([fulltime]=-1,1,0)
=IIf([fulltime]=-1,0,1)

there seems to be some minor differencies... but the principle is same

;-)

0

LVL 31

Expert Comment

ID: 2641772
Dedushka,

Actually not.  But this is the idea I used in commenting to another question.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10312634

Could not spot your comment in the crowd.
0

LVL 2

Expert Comment

ID: 2647149
Hello.  This is a longer way of doing what everyone else has commented on.  I tested this in a form in the Open Event.
You should be able to do the same thing in the Report Open Event.

1. Make 2 queries.  1 with the criteria of Yes and the other with the criteria of No.

2.  Create 2 text boxes on your Report. 1 for Yes and 1 for No.

intCountYes  and  intCountNo

Then this code to your Report and to a Module

Report:
Private Sub Report_Open(Cancel As Integer)

Call CountYes
Me!intCountYes = CountYes

Call CountNo
Me!intCountNo = CountNo

End Sub

Module:
Function CountYes()

Dim Count_Yes As Recordset
Dim db As Database
Set db = CurrentDb
Set Count_Yes = db.OpenRecordset("QueryYes", dbOpenSnapshot)

If Count_Yes.RecordCount < 1 Then
CountYes = Count_Yes.RecordCount
ElseIf Count_Yes.RecordCount > 0 Then
Count_Yes.MoveLast
CountYes = Count_Yes.RecordCount
End If

End Function

Function CountNo()

Dim Count_No As Recordset
Dim db As Database

Set db = CurrentDb
Set Count_No = db.OpenRecordset("QueryNo", dbOpenSnapshot)

If Count_No.RecordCount < 1 Then
CountNo = Count_No.RecordCount
ElseIf Count_No.RecordCount > 0 Then
Count_No.MoveLast
CountNo = Count_No.RecordCount
End If

End Function

Hope this Helps

WonHop

0

Accepted Solution

bird1427 earned 400 total points
ID: 2650528
On your query create two new fields:

yes:IIF([fieldname]="yes",1,0)
no:IIF([fieldname]="no",1,0)

The first field will return a 1 if the value in your field is yes and a 0 if it is not.The second field will return a 1 if the value in your field is no and a 0 if it is not. Once you have the two fields created in your query, you can use them in your report and create a text box with the formula =Sum([yes])in one and =Sum([no])in the other.

Granted, this is not as elegant or as developed as some of the other ways suggested. It is simple but it does work everytime.
0

LVL 7

Expert Comment

ID: 2651295
bird1427,
"a text box with the formula =Sum([yes])in one and =Sum([no])in the other" will not work, because fields [yes] and [no] are not bounded (by the way, it is not good practice to use filds with names that conflicts with keywords, such "yes", "no", etc.).
All the best,
Dedushka

0

Expert Comment

ID: 2651602
You can include the new fields in your report but make them not visible. Then, you put an <unbound> text box in the report footer and create the suggested formula (=Sum([fieldname])) in the unbound text box. Unbound text boxes are used to display the results of a calculation, or to accept input from a user. Data in an unbound text box isn't stored anywhere. The new field names I proposed for the query were just examples. Accessed could call them anything he/she wants. However, it is better to make them as simple as possible so you remeber what they are for.
0

LVL 1

Expert Comment

ID: 2655120
I'll have to agree with Dedushka about the field names.  Sure it may <work> but there is good coding style as well.  What happens 12 mths from now and you go back to it but we are using Access 2001: A Database Oddity?  What about the poor guy that has to maintain this when you are gone?  Wouldn't  SumOfYes  be a better name than   Yes  which may stop working some day and WILL confuse someone?  "Hmmm. A textbox named Yes.  Yes what!?" would be the muttering of your successor.  As you pointed out, it should be something "you remember what they are for".

Another thing about reserved words is that they are !reserved!.  This means that MS could decide to do anything they want with them and not even feel guilty that your app breaks.

Sorry to butt in but I don't want anyone making more work for themselves later... that's what bosses are for. <grin>
0

LVL 1

Expert Comment

ID: 6820213
Fixed the -1 problem on this question.
Escrow points corrected.

kb
Community Support Moderator
Experts Exchange
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last weekâ€™s ManageEngine webinar, where attendees received a comprehensive look at the maâ€¦
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Whatâ€™s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macroâ€™s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦
###### Suggested Courses
Course of the Month9 days, 5 hours left to enroll