#Error in Report totals

J

jennifer

The columns that I am trying to Total are named "Payout" and "Deducts." As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]). When I
run the report I am getting a #Error message. I am not sure what this means.
There are null values in both columns if that matters. Thanks for your
help!
 
V

VICKY LOPEZ

jennifer said:
The columns that I am trying to Total are named "Payout" and "Deducts."
As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]). When I
run the report I am getting a #Error message. I am not sure what this
means.
There are null values in both columns if that matters. Thanks for your
help!
Click to expand...
 
V

VICKY LOPEZ

jennifer said:
The columns that I am trying to Total are named "Payout" and "Deducts."
As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]). When I
run the report I am getting a #Error message. I am not sure what this
means.
There are null values in both columns if that matters. Thanks for your
help!
Click to expand...
 
J

jennifer

I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has a
UNION join, if that matters. There are negative values, positive values, and
lines that have no data (Im assuming are being read as null values). Thanks

boblarson said:
Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


jennifer said:
The columns that I am trying to Total are named "Payout" and "Deducts." As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]). When I
run the report I am getting a #Error message. I am not sure what this means.
There are null values in both columns if that matters. Thanks for your
help!
Click to expand...
Click to expand...
 
A

Allen Browne

Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different place.

5. If you are still stuck, post the UNION query statement.

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

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

jennifer said:
I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has a
UNION join, if that matters. There are negative values, positive values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

boblarson said:
Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


jennifer said:
The columns that I am trying to Total are named "Payout" and "Deducts."
As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]).
When I
run the report I am getting a #Error message. I am not sure what this
means.
There are null values in both columns if that matters. Thanks for
your
help!
Click to expand...
Click to expand...
Click to expand...
 
J

jennifer

Vicky---

I am not sure your message came across... I only see my message from
earlier. Thanks,

VICKY LOPEZ said:
jennifer said:
The columns that I am trying to Total are named "Payout" and "Deducts."
As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]). When I
run the report I am getting a #Error message. I am not sure what this
means.
There are null values in both columns if that matters. Thanks for your
help!
Click to expand...
Click to expand...
 
J

jennifer

1. I changed the name to Deducts to try and sum that one column, but it is
still giving me #Error.

2. Instead of it being SumOfAmount and SumOfCurr Dedn I changed the column
headings to Payout and Deducts within the report.

3. The data I am trying to sum in a Number. I was Currency, but I changed
it to see if that would help and it did not.

4. I deleted the other calculated field and that did not help. That
calculated field was just =Sum([SumOfAmount])-Sum([SumOfCurr Dedn]).
However, it worked fine.


5. The UNION statement that I am using in the query is as follows:

SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout Store],
Query4.[CountOfDescription], Query4.[MaxOfDate], Query4.[SumOfAmount],
Query4.[Term Date], Query5.PS, Query5.[LastOfName], Query5.[CountOfDeductn
Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period End], Query5.[Term
Date]
FROM Query4 LEFT JOIN Query5
ON Query4.PS = Query5.PS
UNION SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout
Store], Query4.[CountOfDescription], Query4.[MaxOfDate],
Query4.[SumOfAmount], Query4.[Term Date], Query5.PS, Query5.[LastOfName],
Query5.[CountOfDeductn Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period
End], Query5.[Term Date]
FROM Query5 LEFT JOIN Query4
ON Query4.PS = Query5.PS;


Thank you soooo much for your help!


Allen Browne said:
Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different place.

5. If you are still stuck, post the UNION query statement.

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

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

jennifer said:
I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has a
UNION join, if that matters. There are negative values, positive values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

boblarson said:
Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))

--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

The columns that I am trying to Total are named "Payout" and "Deducts."
As
of right now I am using the formula =Sum([Payout])-Sum([Deducts]).
When I
run the report I am getting a #Error message. I am not sure what this
means.
There are null values in both columns if that matters. Thanks for
your
help!
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

You cannot sum a *control* - only a *field* in the report's RecordSource. If
Payout is just the name of a text box, you can't sum that. Try:
=Sum([SumOfAmount])

Once you have that working, try:
=IIf([Report].[HasData], Sum([SumOfAmount]), Null)

The query did not really help much as it appears to be based on other
queries we don't know about. The question about how the query displays the
SumOfAmount field (left- or right-aligned) could still give a clue.

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

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

jennifer said:
1. I changed the name to Deducts to try and sum that one column, but it
is
still giving me #Error.

2. Instead of it being SumOfAmount and SumOfCurr Dedn I changed the
column
headings to Payout and Deducts within the report.

3. The data I am trying to sum in a Number. I was Currency, but I changed
it to see if that would help and it did not.

4. I deleted the other calculated field and that did not help. That
calculated field was just =Sum([SumOfAmount])-Sum([SumOfCurr Dedn]).
However, it worked fine.


5. The UNION statement that I am using in the query is as follows:

SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout Store],
Query4.[CountOfDescription], Query4.[MaxOfDate], Query4.[SumOfAmount],
Query4.[Term Date], Query5.PS, Query5.[LastOfName], Query5.[CountOfDeductn
Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period End], Query5.[Term
Date]
FROM Query4 LEFT JOIN Query5
ON Query4.PS = Query5.PS
UNION SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout
Store], Query4.[CountOfDescription], Query4.[MaxOfDate],
Query4.[SumOfAmount], Query4.[Term Date], Query5.PS, Query5.[LastOfName],
Query5.[CountOfDeductn Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay
Period
End], Query5.[Term Date]
FROM Query5 LEFT JOIN Query4
ON Query4.PS = Query5.PS;


Thank you soooo much for your help!


Allen Browne said:
Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks
the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different
place.

5. If you are still stuck, post the UNION query statement.

jennifer said:
I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has
a
UNION join, if that matters. There are negative values, positive
values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

:

Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))
Click to expand...
Click to expand...
Click to expand...
 
F

Fred

Based on the frequency of mistakes that cause me to see this error message,
also make sure that none of your controls (e.g. boxes) has the same name as
any variables/ fields that you're trying to use in an expression. If so
rename the box.
 
J

jennifer

The items that I am wanting to total are numbers in the Table. I want to
make sure on the basic level that I am attempting to do this correctly. I am
clicking on the abl box and typing the equations that you have been giving me
into an unbound box. The other box that I am getting I believe is the title
box. I tried just =Sum([SumOfAmount]) and that did not work either. The very
first page of my report is showing no values for that column so I tried the
second equation you gave me and still recieved the same #error message where
the total should be. I am not sure what the difference in *control* and
*field* is. I am sorry it took me so long to reply I do not work on Monday
afternoon or Tuesdays.

Allen Browne said:
You cannot sum a *control* - only a *field* in the report's RecordSource. If
Payout is just the name of a text box, you can't sum that. Try:
=Sum([SumOfAmount])

Once you have that working, try:
=IIf([Report].[HasData], Sum([SumOfAmount]), Null)

The query did not really help much as it appears to be based on other
queries we don't know about. The question about how the query displays the
SumOfAmount field (left- or right-aligned) could still give a clue.

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

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

jennifer said:
1. I changed the name to Deducts to try and sum that one column, but it
is
still giving me #Error.

2. Instead of it being SumOfAmount and SumOfCurr Dedn I changed the
column
headings to Payout and Deducts within the report.

3. The data I am trying to sum in a Number. I was Currency, but I changed
it to see if that would help and it did not.

4. I deleted the other calculated field and that did not help. That
calculated field was just =Sum([SumOfAmount])-Sum([SumOfCurr Dedn]).
However, it worked fine.


5. The UNION statement that I am using in the query is as follows:

SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout Store],
Query4.[CountOfDescription], Query4.[MaxOfDate], Query4.[SumOfAmount],
Query4.[Term Date], Query5.PS, Query5.[LastOfName], Query5.[CountOfDeductn
Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period End], Query5.[Term
Date]
FROM Query4 LEFT JOIN Query5
ON Query4.PS = Query5.PS
UNION SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout
Store], Query4.[CountOfDescription], Query4.[MaxOfDate],
Query4.[SumOfAmount], Query4.[Term Date], Query5.PS, Query5.[LastOfName],
Query5.[CountOfDeductn Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay
Period
End], Query5.[Term Date]
FROM Query5 LEFT JOIN Query4
ON Query4.PS = Query5.PS;


Thank you soooo much for your help!


Allen Browne said:
Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks
the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different
place.

5. If you are still stuck, post the UNION query statement.

I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has
a
UNION join, if that matters. There are negative values, positive
values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

:

Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))
Click to expand...
Click to expand...
Click to expand...
 
J

jennifer

Also, if I move the equation up into the detail section it comes up with the
same number over and over again, but if i place it in the footer that is when
I get the #error.

Allen Browne said:
You cannot sum a *control* - only a *field* in the report's RecordSource. If
Payout is just the name of a text box, you can't sum that. Try:
=Sum([SumOfAmount])

Once you have that working, try:
=IIf([Report].[HasData], Sum([SumOfAmount]), Null)

The query did not really help much as it appears to be based on other
queries we don't know about. The question about how the query displays the
SumOfAmount field (left- or right-aligned) could still give a clue.

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

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

jennifer said:
1. I changed the name to Deducts to try and sum that one column, but it
is
still giving me #Error.

2. Instead of it being SumOfAmount and SumOfCurr Dedn I changed the
column
headings to Payout and Deducts within the report.

3. The data I am trying to sum in a Number. I was Currency, but I changed
it to see if that would help and it did not.

4. I deleted the other calculated field and that did not help. That
calculated field was just =Sum([SumOfAmount])-Sum([SumOfCurr Dedn]).
However, it worked fine.


5. The UNION statement that I am using in the query is as follows:

SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout Store],
Query4.[CountOfDescription], Query4.[MaxOfDate], Query4.[SumOfAmount],
Query4.[Term Date], Query5.PS, Query5.[LastOfName], Query5.[CountOfDeductn
Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period End], Query5.[Term
Date]
FROM Query4 LEFT JOIN Query5
ON Query4.PS = Query5.PS
UNION SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout
Store], Query4.[CountOfDescription], Query4.[MaxOfDate],
Query4.[SumOfAmount], Query4.[Term Date], Query5.PS, Query5.[LastOfName],
Query5.[CountOfDeductn Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay
Period
End], Query5.[Term Date]
FROM Query5 LEFT JOIN Query4
ON Query4.PS = Query5.PS;


Thank you soooo much for your help!


Allen Browne said:
Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks
the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different
place.

5. If you are still stuck, post the UNION query statement.

I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has
a
UNION join, if that matters. There are negative values, positive
values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

:

Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))
Click to expand...
Click to expand...
Click to expand...
 
J

jennifer

Also, if I move it up into the MaxOfPayout Store header it will total the
groups using =IIf(Report.HasData,Sum([SumOfCurr Dedn]),Null), but is there a
way for me to get it to have a running total or a final total at the bottom
that has all the payouts and all the deductions totalled without it giving me
the #error answer?

Allen Browne said:
You cannot sum a *control* - only a *field* in the report's RecordSource. If
Payout is just the name of a text box, you can't sum that. Try:
=Sum([SumOfAmount])

Once you have that working, try:
=IIf([Report].[HasData], Sum([SumOfAmount]), Null)

The query did not really help much as it appears to be based on other
queries we don't know about. The question about how the query displays the
SumOfAmount field (left- or right-aligned) could still give a clue.

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

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

jennifer said:
1. I changed the name to Deducts to try and sum that one column, but it
is
still giving me #Error.

2. Instead of it being SumOfAmount and SumOfCurr Dedn I changed the
column
headings to Payout and Deducts within the report.

3. The data I am trying to sum in a Number. I was Currency, but I changed
it to see if that would help and it did not.

4. I deleted the other calculated field and that did not help. That
calculated field was just =Sum([SumOfAmount])-Sum([SumOfCurr Dedn]).
However, it worked fine.


5. The UNION statement that I am using in the query is as follows:

SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout Store],
Query4.[CountOfDescription], Query4.[MaxOfDate], Query4.[SumOfAmount],
Query4.[Term Date], Query5.PS, Query5.[LastOfName], Query5.[CountOfDeductn
Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay Period End], Query5.[Term
Date]
FROM Query4 LEFT JOIN Query5
ON Query4.PS = Query5.PS
UNION SELECT Query4.PS, Query4.[LastOfPartner_Name], Query4.[MaxOfPayout
Store], Query4.[CountOfDescription], Query4.[MaxOfDate],
Query4.[SumOfAmount], Query4.[Term Date], Query5.PS, Query5.[LastOfName],
Query5.[CountOfDeductn Cd], Query5.[SumOfCurr Dedn], Query5.[MaxOfPay
Period
End], Query5.[Term Date]
FROM Query5 LEFT JOIN Query4
ON Query4.PS = Query5.PS;


Thank you soooo much for your help!


Allen Browne said:
Some things to check:

1. Does your report have an Amount field?
It did not in your previous example.

2. Make sure the Name of this text box is not the same as the name of any
field in the report's source query.

3. Run the UNION query itself. Does the field left-align (like text), or
right-align (like numbers)? You won't be able to sum it if Access thinks
the
field is just text.

4. Eliminate any other calculated controls until you get this one sorted
out. Once one field is found to be uncalculatable, Access gives up on
calculating the others, so the fault can be in a completely different
place.

5. If you are still stuck, post the UNION query statement.

I tried both the expression below and
=IIf([Report].[HasData], Sum([Amount]), 0)

I am still getting #Error. I am making a report from a query that has
a
UNION join, if that matters. There are negative values, positive
values,
and
lines that have no data (Im assuming are being read as null values).
Thanks

:

Yes, Nulls would matter. So, try this:
=Sum(Nz([Payout],0))-Sum(Nz([Deducts],0))
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

If you are using the Page Footer it won't work.

Use the Report Footer (or a group footer.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Aggregate Function Error 0
Help with formula please 4
totals for multiple fields 1
TINA - TOTALS OF TOTALS IN A REPORT 15
Wrong Sum in Report Footer 2
Force New Page - Report Totals 2
Change values in report depending on certain criteria 2
Output Date Grouping 1

Top