Calculating in a Report

[Guest]

Just to give you a little history...I enter my date into a form I created
which also puts the information into a table which is called 2006. One of the
fields in both the form & table is titled "At Fault" which has a drop box in
the form with the following...Yes, No or TBD. I then ran a query and then
created a report from that query. Now I'm trying to show in the group footer
how many yes's there are in the "At Fault" field, but I tried the below
formula, but all it gives me is a 0 for every group & that is definitly
incorrect. Could someone please help me, I'm quite frustrated!! Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At Fault]&"'")

 

[Allen Browne]

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads both Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At Fault] &
""")")

 

[Guest]

When I tried the formula below but it gives me E+0 as an answer. I double
checked that I typed it right, any suggestions?? Thanks

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads both Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At Fault] &
""")")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Just to give you a little history...I enter my date into a form I created
which also puts the information into a table which is called 2006. One of
the
fields in both the form & table is titled "At Fault" which has a drop box
in
the form with the following...Yes, No or TBD. I then ran a query and then
created a report from that query. Now I'm trying to show in the group
footer
how many yes's there are in the "At Fault" field, but I tried the below
formula, but all it gives me is a 0 for every group & that is definitly
incorrect. Could someone please help me, I'm quite frustrated!! Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At Fault]&"'")

 

[Allen Browne]

Is this a really narrow text box?
Or did you set the Format of this text box to Scientific or something?

E+0 is scientific notation for zero (i.e no records.)

Try creating a query using the 2006 table.
Drag the * into the grid from the top line of the 2006 table.
Drag the [At Fault] field into the grid.
In the Criteria row under this field, enter:
Yes
On the next row under the criteria in the same column, enter:
No
How many records does the query return?

It it works, switch the query to SQL View (View menu), and paste the SQL
statement that works in a reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When I tried the formula below but it gives me E+0 as an answer. I double
checked that I typed it right, any suggestions?? Thanks

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads both
Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At
Fault] &
""")")

Just to give you a little history...I enter my date into a form I
created
which also puts the information into a table which is called 2006. One
of
the
fields in both the form & table is titled "At Fault" which has a drop
box
in
the form with the following...Yes, No or TBD. I then ran a query and
then
created a report from that query. Now I'm trying to show in the group
footer
how many yes's there are in the "At Fault" field, but I tried the below
formula, but all it gives me is a 0 for every group & that is definitly
incorrect. Could someone please help me, I'm quite frustrated!! Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At Fault]&"'")

 

[Guest]

The text box was narrow so I made it bigger & the numbers appeared, but it
still is not giving me a total for just "yes" At Fault damages, it's now
giving me the total for both "yes" & "no" At Fault damages. I just want "yes"
At Fault. What would I put in the control source for that? Thanks so much

Is this a really narrow text box?
Or did you set the Format of this text box to Scientific or something?

E+0 is scientific notation for zero (i.e no records.)

Try creating a query using the 2006 table.
Drag the * into the grid from the top line of the 2006 table.
Drag the [At Fault] field into the grid.
In the Criteria row under this field, enter:
Yes
On the next row under the criteria in the same column, enter:
No
How many records does the query return?

It it works, switch the query to SQL View (View menu), and paste the SQL
statement that works in a reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When I tried the formula below but it gives me E+0 as an answer. I double
checked that I typed it right, any suggestions?? Thanks

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads both
Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At
Fault] &
""")")

Just to give you a little history...I enter my date into a form I
created
which also puts the information into a table which is called 2006. One
of
the
fields in both the form & table is titled "At Fault" which has a drop
box
in
the form with the following...Yes, No or TBD. I then ran a query and
then
created a report from that query. Now I'm trying to show in the group
footer
how many yes's there are in the "At Fault" field, but I tried the below
formula, but all it gives me is a 0 for every group & that is definitly
incorrect. Could someone please help me, I'm quite frustrated!! Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At Fault]&"'")

 

[Allen Browne]

If you just want Yes:
=DCount("*","[2006]","[At Fault]='Yes'")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The text box was narrow so I made it bigger & the numbers appeared, but it
still is not giving me a total for just "yes" At Fault damages, it's now
giving me the total for both "yes" & "no" At Fault damages. I just want
"yes"
At Fault. What would I put in the control source for that? Thanks so much

Is this a really narrow text box?
Or did you set the Format of this text box to Scientific or something?

E+0 is scientific notation for zero (i.e no records.)

Try creating a query using the 2006 table.
Drag the * into the grid from the top line of the 2006 table.
Drag the [At Fault] field into the grid.
In the Criteria row under this field, enter:
Yes
On the next row under the criteria in the same column, enter:
No
How many records does the query return?

It it works, switch the query to SQL View (View menu), and paste the SQL
statement that works in a reply.

When I tried the formula below but it gives me E+0 as an answer. I
double
checked that I typed it right, any suggestions?? Thanks

:

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads
both
Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field
that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At
Fault] &
""")")

Just to give you a little history...I enter my date into a form I
created
which also puts the information into a table which is called 2006.
One
of
the
fields in both the form & table is titled "At Fault" which has a
drop
box
in
the form with the following...Yes, No or TBD. I then ran a query and
then
created a report from that query. Now I'm trying to show in the
group
footer
how many yes's there are in the "At Fault" field, but I tried the
below
formula, but all it gives me is a 0 for every group & that is
definitly
incorrect. Could someone please help me, I'm quite frustrated!!
Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At
Fault]&"'")

 

[Guest]

That works for the report footer, which I need also, Thank you. But how can I
get it to work for the group footer. I used the same formula, but it gives me
the total for the whole report, not for each of the specific groups. Thanks

If you just want Yes:
=DCount("*","[2006]","[At Fault]='Yes'")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The text box was narrow so I made it bigger & the numbers appeared, but it
still is not giving me a total for just "yes" At Fault damages, it's now
giving me the total for both "yes" & "no" At Fault damages. I just want
"yes"
At Fault. What would I put in the control source for that? Thanks so much

Is this a really narrow text box?
Or did you set the Format of this text box to Scientific or something?

E+0 is scientific notation for zero (i.e no records.)

Try creating a query using the 2006 table.
Drag the * into the grid from the top line of the 2006 table.
Drag the [At Fault] field into the grid.
In the Criteria row under this field, enter:
Yes
On the next row under the criteria in the same column, enter:
No
How many records does the query return?

It it works, switch the query to SQL View (View menu), and paste the SQL
statement that works in a reply.

When I tried the formula below but it gives me E+0 as an answer. I
double
checked that I typed it right, any suggestions?? Thanks

:

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads
both
Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field
that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At
Fault] &
""")")

Just to give you a little history...I enter my date into a form I
created
which also puts the information into a table which is called 2006.
One
of
the
fields in both the form & table is titled "At Fault" which has a
drop
box
in
the form with the following...Yes, No or TBD. I then ran a query and
then
created a report from that query. Now I'm trying to show in the
group
footer
how many yes's there are in the "At Fault" field, but I tried the
below
formula, but all it gives me is a 0 for every group & that is
definitly
incorrect. Could someone please help me, I'm quite frustrated!!
Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At
Fault]&"'")

 

[Allen Browne]

In the group footer you could try:
= - Sum(Nz([At Fault] = 'Yes'), False)

The expression:
([At Fault] = 'Yes')
is True when the text is "Yes", False when it is something else, and Null if
it has no value. The Nz() converts the Null to False. Internally, Access
uses -1 for True, and 0 for False, so summing the expression and negating
the result gives the count of when the expression is True.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That works for the report footer, which I need also, Thank you. But how
can I
get it to work for the group footer. I used the same formula, but it gives
me
the total for the whole report, not for each of the specific groups.
Thanks

If you just want Yes:
=DCount("*","[2006]","[At Fault]='Yes'")

The text box was narrow so I made it bigger & the numbers appeared, but
it
still is not giving me a total for just "yes" At Fault damages, it's
now
giving me the total for both "yes" & "no" At Fault damages. I just want
"yes"
At Fault. What would I put in the control source for that? Thanks so
much

:

Is this a really narrow text box?
Or did you set the Format of this text box to Scientific or something?

E+0 is scientific notation for zero (i.e no records.)

Try creating a query using the 2006 table.
Drag the * into the grid from the top line of the 2006 table.
Drag the [At Fault] field into the grid.
In the Criteria row under this field, enter:
Yes
On the next row under the criteria in the same column, enter:
No
How many records does the query return?

It it works, switch the query to SQL View (View menu), and paste the
SQL
statement that works in a reply.

When I tried the formula below but it gives me E+0 as an answer. I
double
checked that I typed it right, any suggestions?? Thanks

:

There are several issues here.

1. You need to use OR, not AND in the 3rd string.
There are no records in your table where the [At Fault] field reads
both
Yes
AND No at the same time.

2. The closing bracket after the first asterisk is not correct.

3. You may need square brackets around the 2006 (any table or field
that
starts with a number.)

4. Brackets, while optional, can help.

=DCount("*","[2006]","([At Fault]='Yes') OR ([At Fault] = """ & [At
Fault] &
""")")

Just to give you a little history...I enter my date into a form I
created
which also puts the information into a table which is called
2006.
One
of
the
fields in both the form & table is titled "At Fault" which has a
drop
box
in
the form with the following...Yes, No or TBD. I then ran a query
and
then
created a report from that query. Now I'm trying to show in the
group
footer
how many yes's there are in the "At Fault" field, but I tried the
below
formula, but all it gives me is a 0 for every group & that is
definitly
incorrect. Could someone please help me, I'm quite frustrated!!
Thanks

=DCount("*"),"2006","[At Fault]='Yes' And[At Fault]='"&[At
Fault]&"'")

 

[Marshall Barton]

In the group footer you could try:
= - Sum(Nz([At Fault] = 'Yes'), False)

The expression:
([At Fault] = 'Yes')
is True when the text is "Yes", False when it is something else, and Null if
it has no value. The Nz() converts the Null to False. Internally, Access
uses -1 for True, and 0 for False, so summing the expression and negating
the result gives the count of when the expression is True.

Slight refinement?

Since the aggregate functions ignore Null values, the Nz is
unecessary:

=-Sum([At Fault] = "Yes")

 

[Guest]

It worked!! Thank you so much for all of your help, I really appreciated it.

In the group footer you could try:
= - Sum(Nz([At Fault] = 'Yes'), False)

The expression:
([At Fault] = 'Yes')
is True when the text is "Yes", False when it is something else, and Null if
it has no value. The Nz() converts the Null to False. Internally, Access
uses -1 for True, and 0 for False, so summing the expression and negating
the result gives the count of when the expression is True.

Slight refinement?

Since the aggregate functions ignore Null values, the Nz is
unecessary:

=-Sum([At Fault] = "Yes")