Counting specific records using a expression in a text box

[Guest]

I am using a text box control to count specific records in a table field but
can't get it to work. If I count all the records it's simple Count(*). But
what I want to do is count the content of a boolean control that takes a
check in it. Let's say the table has one hundred records, the control that is
used to enter data into the field (which is a true/fales, yes/no field) is
either checked or not. If it is checked I don't want to count it, this
indicates that a matter has been cleared. So, if twenty records are checked
my record counter should return 80 since these matters are still open. I
tried this code but it did not work: DCount("[Myfield]", "Mytable",
"[Myfield] Is Null") Anyone out there know how to handle this? Thanks

 

[Douglas J. Steele]

From the Help file:

"The DCount function doesn't count records that contain Null values in the
field referenced by expr, unless expr is the asterisk (*) wildcard
character. If you use an asterisk, the DCount function calculates the total
number of records, including those that contain Null fields. The following
example calculates the number of records in an Orders table."

Try

DCount("*", "Mytable", "[Myfield] Is Null")

 

[Guest]

That nice but I don't want to count all (*) the records in the table I want
to count what is in a table field. Do you have code to do this?

From the Help file:

"The DCount function doesn't count records that contain Null values in the
field referenced by expr, unless expr is the asterisk (*) wildcard
character. If you use an asterisk, the DCount function calculates the total
number of records, including those that contain Null fields. The following
example calculates the number of records in an Orders table."

Try

DCount("*", "Mytable", "[Myfield] Is Null")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using a text box control to count specific records in a table field
but
can't get it to work. If I count all the records it's simple Count(*). But
what I want to do is count the content of a boolean control that takes a
check in it. Let's say the table has one hundred records, the control that
is
used to enter data into the field (which is a true/fales, yes/no field) is
either checked or not. If it is checked I don't want to count it, this
indicates that a matter has been cleared. So, if twenty records are
checked
my record counter should return 80 since these matters are still open. I
tried this code but it did not work: DCount("[Myfield]", "Mytable",
"[Myfield] Is Null") Anyone out there know how to handle this? Thanks

 

[Larry Daugherty]

Hi,

You didn't tell us how it fails but I'd guess that it returns a few or no
records.

try making the where clause "[Myfield] = False" or "[Myfield] - 0". To make
it more bulletproof, make either True or False the default value so that no
Null can sneak through.

HTH

 

[Dirk Goldgar]

I am using a text box control to count specific records in a table
field but can't get it to work. If I count all the records it's
simple Count(*). But what I want to do is count the content of a
boolean control that takes a check in it. Let's say the table has one
hundred records, the control that is used to enter data into the
field (which is a true/fales, yes/no field) is either checked or not.
If it is checked I don't want to count it, this indicates that a
matter has been cleared. So, if twenty records are checked my record
counter should return 80 since these matters are still open. I tried
this code but it did not work: DCount("[Myfield]", "Mytable",
"[Myfield] Is Null") Anyone out there know how to handle this? Thanks

I don't think a boolean field can ever be Null. Try either

=DCount("*", "MyTable", "[MyField]=False")

or

=Abs(Sum([MyField]=False))

The latter must be in the form footer or header section.

 

[Douglas J. Steele]

What do you expect to be different between what you were trying to use:

DCount("[Myfield]", "Mytable", "[Myfield] Is Null")

and what I suggested:

DCount("*", "Mytable", "[Myfield] Is Null")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That nice but I don't want to count all (*) the records in the table I
want
to count what is in a table field. Do you have code to do this?

From the Help file:

"The DCount function doesn't count records that contain Null values in
the
field referenced by expr, unless expr is the asterisk (*) wildcard
character. If you use an asterisk, the DCount function calculates the
total
number of records, including those that contain Null fields. The
following
example calculates the number of records in an Orders table."

Try

DCount("*", "Mytable", "[Myfield] Is Null")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using a text box control to count specific records in a table field
but
can't get it to work. If I count all the records it's simple Count(*).
But
what I want to do is count the content of a boolean control that takes
a
check in it. Let's say the table has one hundred records, the control
that
is
used to enter data into the field (which is a true/fales, yes/no field)
is
either checked or not. If it is checked I don't want to count it, this
indicates that a matter has been cleared. So, if twenty records are
checked
my record counter should return 80 since these matters are still open.
I
tried this code but it did not work: DCount("[Myfield]", "Mytable",
"[Myfield] Is Null") Anyone out there know how to handle this? Thanks