Incorrect 'Sum' in Report Footer (1 Viewer)

[klix]

I'm trying to sum a bunch of account fields (it's a financial db) in a report to see if they balance. I used the formula '=Sum([Balance])' in a text box in the Report Footer but unfortunately the summed value displayed is incorrect. When I visually added the numbers in the print preview I get a sum of 0, which is what it should be. However 0.1 is displayed. Any thoughts why? (Maybe I have a 'slow' version of Access?

 

[RainLover]

Is [Balance] a field in the Recordsource or is it a Control on the Report.

 

[Kiwiman]

Howzit

Is your report formatted to 2dp where in fact the actual values are more, leading to the display of 0.01?

 

[klix]

Sorry for the delayed answer to your questions...I do appreciate the help.

Unfortunately I'm not an experienced user of Access so I'm not privy to the jargon. I don't really know what 2dp, Recordsource, or Control is. What I have is a query with a 'Sum' total for each separate 'account'. This report pulls each 'account' and its corresponsing total from that query and should add them. If either of you could explain in a bit more detail, or ask the question in a different way (to a novice), we can get going on the problem better. Thanks.

 

[RainLover]

2 dp = 2 Decimal Places.

Look at your Table the Data Type of the Field should be Number. The Field Size should be Double and the Decimal Places 2

Or The Data type could be Currency and the Decimal Places 2

Either way may help.

You find Fields in Tables and Queries. You have Controls on Forms. A Textbox on a Form is a control and it's Control Source is a Field from a Query or Table. The Record Source for a Form is a Table, Query or a SQL Statement.

Hope this helps a little. If you need further clarification use Help in Access.

 

[klix]

Thanks for the info...now I see what Kiwiman meant in his first question. To answer that: the fields on my table are set to Currency, 2dp, so the results on the report aren't rounded. Now to answer your first question, I believe [Balance] is a control on the report. It comes from a query which subtracts [Credit] from [Debit], both of which fields come from a table.

In other words: I have a table with a bunch of financial transactions, each one having one number in either [Debit] or [Credit]. Then I have a query with the following expression: 'Balance: Sum([Debit])-Sum([Credit])'. Then I have the report based off that query. Because this is double-entry bookkeeping, all the balances sum to 0. However when I sum the 'Balance' control (is that correct?) in the report footer, 0.1 is displayed.

Does that clarify? Is there any further help you can give at this point?

 

[klix]

Nevermind. I fixed the problem. I had entered one of the numbers wrong. No kidding it balanced everywhere but on the report! Sorry for the bother - I honestly thought I had a problem with the formula or something related.

 

[sirkistova]

Hello,
I hit "Post reply" because I couldn't find "Post new thread" or similar, so, looked for a similar problem ...
I also have a "Balance" field in a report.
I try, with no success, to have "SumBalance" at the report footer.
I tried: =Sum([Balance])
Access crashes when I run the report.
I tried: Just Balance, with "over all" at "running sum" attribute. It works when I place the field in the "detail" part of report. But I need it in the footer. In the footer, in displays 0, which is incorrect, and, "Balance" field in many records under "detail" misteriously show up wrong and negative. ??
Balance is a field in the query that is the "record source" of the report.
Help please.
Thanks
Tova

 

[RainLover]

Please post the SQL of the Record Source.

 

[sirkistova]

The SQL of the query that is the record source of the report:

SELECT IIf(ReciprocityCount>10,(ReciprocityCount-10),0) AS ReciprocityBillCount, ReciprocityBillCount*5 AS ReciprocityCost, RegularCount*5 AS RegularCost, (ReciprocityCost+RegularCost+ReciprocityOptCost) AS TotalCost, (TotalCost-AmountPaid) AS BalanceDue, (AmountPaid*1) AS AmountPaidCur, *
FROM qryInvoiceSendCnts;

The field "BalanceDue" is the field that I would like to sum up at the report footer.
Thanks
Tova

 

[Brianwarnock]

You said that you put =Sum([Balance]), Balance is a field on your report, you need =Sum([balancedue]) the field in your source query.

Brian

 

[RainLover]

Brian

I think you are correct.

 

[sirkistova]

I meant BalanceDue.
I try, with no success, to have "SumBalanceDue" at the report footer.
I tried: =Sum([BalanceDue])
Access crashes when I run the report.
I tried: Just BalanceDue, with "over all" at "running sum" attribute. It works when I place the field in the "detail" part of report. But I need it in the footer. In the footer, in displays 0, which is incorrect, and, "BalanceDue" field in many records under "detail" misteriously shows up wrong and negative. ??
BalanceDue is a field in the query that is the "record source" of the report.
Help please.
Thanks
Tova

 

[RainLover]

I think you need to post a cut down version of your Database

 

[sirkistova]

How do I post a cut down version of the Database?

 

[RainLover]

Copy the database under a different name.

Delete all the Tables queries etc that do not affect what we are dealing with.

Do a Compact and Repair. Then zip it and post it.

Make sure it is not Version 2007 as I do not have that version on my machine.

 

[sirkistova]

Meanwhile while I am working on the cut down db, (hope to do it later today),
Is it possible to have a record source for the report's footer, other than the record source for the details? If yes, it might solve my problem; I'll make a query that sums up BalanceDue, and use it as record source for the footer.
Thanks
Tova

 

[RainLover]

No

But you could use a sub form.

 

[sirkistova]

Thanks!
I used a select-sum query and a sub form - it works!
Thanks!
Tova