Re: Counting only Yes values from a Yes/No field.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Any non-zero, non-Null value will be displayed with a check.

If you click on a check box it will set its value to
True (-1), False (0) or, if it's TripleState property is
set, Null. However, the value of the control can be set
using code the same way as any other control, subject to the
constraints imposed by its bound field's data type. (Note
that this can get rather silly when binding a text field to
a check box ;-)
--
Marsh
MVP [MS Access]


Klatuu wrote:
>Okay, Gotcha.
>I have never used a check box for other than Boolean fields. If you use it
>on a different data type, how will it know when to show checked or unchecked
>and what value will it assign for a new record in either case? Would it have
>to been done via the contor's Before Update?
>
>
>"Marshall Barton" wrote:
>> TriState is a property of a check box control, it does not
>> apply to a field in a table/query. Furthermore a TriState
>> checkbox can have any value, limited only by the type of the
>> numeric (or even a date) field it is bound to, any non-zero,
>> non-Null value in the bound field will display a check in
>> the check box.
>>
>> No, you are not misunderstanding about what will be counted,
>> I'm just not saying it very well. What I said applies to
>> both Count(field) and DCount("field", "table") without
>> criteria. Using criteria on a boolean field, and, once
>> again, ignoring the complexity of a filtered form/report
>> recordset,
>> DCount("field", "table", "field = True")
>> is equivalent to any of these commonly used aggregates:
>> Count(IIf(field, x, Null)
>> Sum(IIf(field, 1, 0)
>> Sum(IIf(field, 1, Null)
>> Abs(Sum(field))
>>
>> I know that you understand all that. The point I have been
>> trying to address is that it can be very difficult to
>> specify the proper criteria in a Domain Aggregate function
>> to match the form/report/query filtering.
>>
>> And I was really aiming the comment about zero and Null at
>> other people who may be following this discussion that may
>> not have made that distinction yet. I apologize, I should
>> have stated that instead of assuming it would be understood.
>>
>>
>> Klatuu wrote:
>> >You statement regarding Tri-state is not correct. A tri-state check box will
>> >return -1 for true, 0 for false, and Null for no selection.
>> >
>> >I think I may be misunderstanding what you are saying about what will and
>> >will not be counted. If your criteria requests only True, then you will get
>> >a count of records with true in the field. It will not count True and Null
>> >or any other value.
>> >
>> >I will let the comment about 0 and null being different go. I'm a bit more
>> >advanced than that.
>> >
>> >"Marshall Barton" wrote:
>> >> A DCount is just a function wrapper around a temporary
>> >> query. Except for the criteria issues, the items that are
>> >> counted are the same. If performance is not an issue and
>> >> leaving aside the complex problems of applying a form's
>> >> filtering criteria, the only advantage of using DCount is
>> >> that you can use a criteria to select the only the True
>> >> values to count.
>> >>
>> >> As with all aggregation functions, except Count(*),
>> >> Count(field) and DCount("field"," table") will both count
>> >> all non-Null values. So, in the case of a field displayed
>> >> as a triple state check box, Count(field) will count both
>> >> the True and False values. It will not count the Null
>> >> records, nor will it count only the True records (zero is
>> >> not the same as Null).
>> >>
>> >> Note that a field displayed as a triple state check box must
>> >> be a numeric type such as Integer, because a boolean field
>> >> can never be Null.
>> >>
>> >>
>> >> Klatuu wrote:
>> >> >My original suggestion was a DCount. Are you saying that if a boolean field
>> >> >were bound to a tri-state control and thus had a null value, that those rows
>> >> >would be counted with the Trues? What about the Falses?
>> >> >
>> >> >We all have our preferences and styles. It is good that there is this group
>> >> >to share and debate information. I have learned a lot here and it has
>> >> >improved what I do.
>> >> >
>> >> >
>> >> >> Klatuu wrote:
>> >> >> >True, but in this case, there is no mention of a form. His basic question was:
>> >> >> >
>> >> >> >how I can count only the positive responses from a yes no field.
>> >> >> >
>> >> >> >We don't know where he wants to use this. If it is a query or a textbox on
>> >> >> >a form or report, then the Sum() would be better than a Domain Aggregate
>> >> >> >function, but the Count() would be better because it is straight forward and
>> >> >> >does not require an addition function or statement. like:
>> >> >> >Abs(Sum([checkfield]))
>> >> >> >or
>> >> >> >Sum([checkfield]) * -1
>> >> >>
>> >> >>
>> >> >"Marshall Barton" wrote:
>> >> >> Well, I assumed that since the question was asked in a Forms
>> >> >> newsgroup, the question related to a form situation, but,
>> >> >> you're right, it was never explicitly stated.
>> >> >>
>> >> >> As for counting records with True values, you can not use a
>> >> >> simple Count(field) because Count will count all non-Null
>> >> >> values, which includes the records with a value of False as
>> >> >> well as True. If you want to use Count, then one way is to
>> >> >> use an expression such as:
>> >> >> Count(IIf(field, x, Null))
>> >> >> where x can be any non-Null literal.
>> >> >>
>> >> >> Personally I think it's clearer, easier and faster to use
>> >> >> Sum as Fred suggested, but that's your call.
>> >>
>>
>>

.

Relevant Pages

  • Re: Display all results, even zeros, nulls, and blanks
    ... Thanks John! ... for your requisite criteria and Is Null!! ... way to display all regions in a query. ... display all the results from a table when the results may be zero. ...
    (microsoft.public.access.queries)
  • Re: Making A Search Form Like Albert Kallals Form
    ... > wanting something similar to Albert Kallal's search form ... > I've set up the continuous form which displays the vendor ... Since the display area is a sub-form, ... To do so I assume I will use a query criteria ...
    (microsoft.public.access.forms)
  • Re: Date criteria
    ... I'm sorry you had a hard time finding the solution, Bruce, but I don't think ... it is documented that Variant is the only data type that can accept a Null ... every year on the expiration month and day a notice needs to ... >>> criteria in I applied criteria to filter out records with ...
    (microsoft.public.access.queries)
  • Re: Auto Filter-VB
    ... This is from "Excel 2002 VBA Programmer's Reference" ... create a criteria string that will locate an exact match in all locales. ... Can you use Auto Filter w VB to display only certain years in a spreadsheet ...
    (microsoft.public.excel.programming)
  • RE: Criteria in calculated field - unexpected prompt for input
    ... My data type mismatch error is occuring when I enter a criteria in the ... calculated field that uses the function Datediff. ... but not in the same query that created them. ...
    (microsoft.public.access.queries)