COUNT RECORDS WITH BOOLEEN

[Guest]

I have a report with three column's, each with a number value when selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
each column is the value of each Booleen field.

 

[Jeff Boyce]

Nick

Consider a "Totals" query, grouped by the values in the field that holds
these 3 values.

 

[John Spencer]

Try the following to count the number of times [(1)Injury] = 1
=Abs(Sum([(1)Injury]=1))


each column is the value of each Booleen field.

 

[Guest]

After a closer look at the query on the report, I found this (1)Injury:
IIf([Injury1]=1,"X",""). Now how do I get a totals for each column based on
this one field?

Nick

Consider a "Totals" query, grouped by the values in the field that holds
these 3 values.

--
Regards

Jeff Boyce
<Office/Access MVP>

I have a report with three column's, each with a number value when selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
each column is the value of each Booleen field.

 

[Michel Walsh]

Hi,


If you are in a detail section and wish for a total of these details, use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to a
group.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I did try your suggestion but get an error message on the report. I am not
sure I put in the right "TablenameHere" in the statment.
Thanks

Hi,


If you are in a detail section and wish for a total of these details, use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to a
group.



Hoping it may help,
Vanderghast, Access MVP

I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
each column is the value of each Booleen field.

 

[Michel Walsh]

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I also
assume the real field name is exactly (1)injury without space. When we
use [ ], the name inside it must be exactly match the (field) name, with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP

I did try your suggestion but get an error message on the report. I am not
sure I put in the right "TablenameHere" in the statment.
Thanks

Hi,


If you are in a detail section and wish for a total of these details, use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to a
group.



Hoping it may help,
Vanderghast, Access MVP

I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned
in
each column is the value of each Booleen field.

 

[Jeff Boyce]

Nick

I'm not there, I don't know what you mean by "for each column".

Are you saying that you have more than one field in the underlying table, or
data displayed in more than one column on your report? Are you working with
a cross-tab report?

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

After a closer look at the query on the report, I found this (1)Injury:
IIf([Injury1]=1,"X",""). Now how do I get a totals for each column based on
this one field?

Nick

Consider a "Totals" query, grouped by the values in the field that holds
these 3 values.

--
Regards

Jeff Boyce
<Office/Access MVP>

I have a report with three column's, each with a number value when selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get

a
total

for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
each column is the value of each Booleen field.

 

[Guest]

Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I also
assume the real field name is exactly (1)injury without space. When we
use [ ], the name inside it must be exactly match the (field) name, with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP

I did try your suggestion but get an error message on the report. I am not
sure I put in the right "TablenameHere" in the statment.
Thanks

Hi,


If you are in a detail section and wish for a total of these details, use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to a
group.



Hoping it may help,
Vanderghast, Access MVP


I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned
in
each column is the value of each Booleen field.

 

[Michel Walsh]

Hi,


The first argument of the iif should involved the exact field name. Is the
field name is [injury] or [(1)Injury]. I assumed it was (1)Injury, and
that you were not to make the comparison. Now, it sounds like the field name
is Injury and that we have to make the comparison, so, I would try:


= DSum( " iif( injury = 1, 1, 0 ) " , "Injury1" )


Since Injury and Injury1 are valid names, we don't have to use [ ] around
them, in that case.


Hoping it may help,
Vanderghast, Access MVP

Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is
Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I also
assume the real field name is exactly (1)injury without space. When
we
use [ ], the name inside it must be exactly match the (field) name, with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP

I did try your suggestion but get an error message on the report. I am
not
sure I put in the right "TablenameHere" in the statment.
Thanks

:

Hi,


If you are in a detail section and wish for a total of these details,
use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to
a
group.



Hoping it may help,
Vanderghast, Access MVP


I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder
with a
number of "2" and (3)Other illness with a number of 3. How can I
get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value
returned
in
each column is the value of each Booleen field.

 

[Guest]

I am using the equation you sugested but am still gettin an Error in the box.
=DSum("iif((1)Injury=1,1,0)","Injury1")
Just in case the information I gave you wasincomplete, I look at all this
information and am not sure I have given you enough. The table name is
[Injury1] and the text box attached to the query is [(1)Injury]. I hope this
helps

Hi,


The first argument of the iif should involved the exact field name. Is the
field name is [injury] or [(1)Injury]. I assumed it was (1)Injury, and
that you were not to make the comparison. Now, it sounds like the field name
is Injury and that we have to make the comparison, so, I would try:


= DSum( " iif( injury = 1, 1, 0 ) " , "Injury1" )


Since Injury and Injury1 are valid names, we don't have to use [ ] around
them, in that case.


Hoping it may help,
Vanderghast, Access MVP

Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is
Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I also
assume the real field name is exactly (1)injury without space. When
we
use [ ], the name inside it must be exactly match the (field) name, with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP



I did try your suggestion but get an error message on the report. I am
not
sure I put in the right "TablenameHere" in the statment.
Thanks

:

Hi,


If you are in a detail section and wish for a total of these details,
use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum to
a
group.



Hoping it may help,
Vanderghast, Access MVP


I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder
with a
number of "2" and (3)Other illness with a number of 3. How can I
get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value
returned
in
each column is the value of each Booleen field.

 

[Michel Walsh]

Hi,


Then, try:


=DSum( " iif( [(1)Injury] = 1 , 1, 0 ) " , "Injury1" )



Note that the control name is not as important as the FIELD NAME of the
query. Both can be with the same also.

Hoping it may help,
Vanderghast, Access MVP

I am using the equation you sugested but am still gettin an Error in the
box.
=DSum("iif((1)Injury=1,1,0)","Injury1")
Just in case the information I gave you wasincomplete, I look at all this
information and am not sure I have given you enough. The table name is
[Injury1] and the text box attached to the query is [(1)Injury]. I hope
this
helps

Hi,


The first argument of the iif should involved the exact field name. Is
the
field name is [injury] or [(1)Injury]. I assumed it was (1)Injury,
and
that you were not to make the comparison. Now, it sounds like the field
name
is Injury and that we have to make the comparison, so, I would
try:


= DSum( " iif( injury = 1, 1, 0 ) " , "Injury1" )


Since Injury and Injury1 are valid names, we don't have to use [ ]
around
them, in that case.


Hoping it may help,
Vanderghast, Access MVP

Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is
Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.
:

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I
also
assume the real field name is exactly (1)injury without space.
When
we
use [ ], the name inside it must be exactly match the (field) name,
with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP



I did try your suggestion but get an error message on the report. I
am
not
sure I put in the right "TablenameHere" in the statment.
Thanks

:

Hi,


If you are in a detail section and wish for a total of these
details,
use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum
to
a
group.



Hoping it may help,
Vanderghast, Access MVP


I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder
with a
number of "2" and (3)Other illness with a number of 3. How can I
get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value
returned
in
each column is the value of each Booleen field.

 

[Guest]

Thanks, I tried your suggestion and still receive an error in the box. I
played with it some and some how found that this equation works
=-sum((1)Injury ="X"). I am not sure why it gives me a negative number, but
by add "-" before the sum the total for are correct. Thanks again

Hi,


Then, try:


=DSum( " iif( [(1)Injury] = 1 , 1, 0 ) " , "Injury1" )



Note that the control name is not as important as the FIELD NAME of the
query. Both can be with the same also.

Hoping it may help,
Vanderghast, Access MVP

I am using the equation you sugested but am still gettin an Error in the
box.
=DSum("iif((1)Injury=1,1,0)","Injury1")
Just in case the information I gave you wasincomplete, I look at all this
information and am not sure I have given you enough. The table name is
[Injury1] and the text box attached to the query is [(1)Injury]. I hope
this
helps

Hi,


The first argument of the iif should involved the exact field name. Is
the
field name is [injury] or [(1)Injury]. I assumed it was (1)Injury,
and
that you were not to make the comparison. Now, it sounds like the field
name
is Injury and that we have to make the comparison, so, I would
try:


= DSum( " iif( injury = 1, 1, 0 ) " , "Injury1" )


Since Injury and Injury1 are valid names, we don't have to use [ ]
around
them, in that case.


Hoping it may help,
Vanderghast, Access MVP



Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is
Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.
:

Hi,


You can try the expression in the Debug Immediate Window.


? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.


You have to use the real table name of the table you really use. I
also
assume the real field name is exactly (1)injury without space.
When
we
use [ ], the name inside it must be exactly match the (field) name,
with
space in it, if any.





Hoping it may help
Vanderghast, Access MVP



I did try your suggestion but get an error message on the report. I
am
not
sure I put in the right "TablenameHere" in the statment.
Thanks

:

Hi,


If you are in a detail section and wish for a total of these
details,
use
DSUM syntax


=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )


You can also use the third argument if you want to restrict the sum
to
a
group.



Hoping it may help,
Vanderghast, Access MVP


I have a report with three column's, each with a number value when
selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder
with a
number of "2" and (3)Other illness with a number of 3. How can I
get a
total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value
returned
in
each column is the value of each Booleen field.