Problem with sum in form footer 1

[BitZero]

I have a bound continuous form, and in the footer is a text field with a control source of "=sum([TotalPaid])". The format is "standard" (3,456.79), with decimal places = 2. The sum is getting computed, but it always gives "00" to the right of the decimal point. For example, it will show 12,345.00 instead of 12.345.67. What is happening to the numbers to the right of the decimal? I'm using Access 2003.
Thanks

 

[boblarson]

What datatype in the table is TotalPaid?

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[BitZero]

Data Type = Number
Field Size = Decimal
Format is blank
Precision = 12
Scale = 2
Decimal Places = 2

 

[dhookom]

Can you provide these properties of the text box in the footer:
Control Source:
Format:
Decimal Places:
Input Mask:

Also, can we assume TotalPaid is a simple field name in your record source of the form with no totaling or formatting?


Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

Here's one piece of info I neglected to give (my apologies): the form is based on a query, which has a group by, that looks like this:

select dept, sum(paid) as TotalPaid
from depthistory
group by dept


In the resulting query, numbers show up to the right of the decimal point for TotalPaid for each dept. But they get lost on the form footer.

Here's the properties of the text box on the footer:
Control Source: =Sum([TotalPaid])
Format: Standard
Decimal Places: 2
Input Mask: (blank)

 

[dhookom]

To refresh Bob's question:
What datatype in the table is Paid?

What do you see as the value in the column when the form's record source is viewed in datasheet? Are there decimal values?

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

Data type is the same:

Data Type = Number
Field Size = Decimal
Format is blank
Precision = 12
Scale = 2
Decimal Places = 2

In datasheet view, you can see the decimal values.

 

[dhookom]

I would try drop a new text box in the form footer and then one by one add or edit properties.

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

I've been trying different properties, but still no success. It seems to be related to a query with a group by and a sum. If the record source is a table, it works. If the record source is a simple query, it works. If the record source is a query with group by and sum, it doesn't work. In all three cases, the properties for the text field are the same.

 

[dhookom]

Can you provide the SQL view of your record source query?

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

select dept, sum(paid) as TotalPaid
from depthistory
group by dept


Thanks for your time

 

[dhookom]

FYI: I reviewed the same MDB file and forms but my results were different than BitZero. The form footer correctly displayed the entire value with decimals.

I'm at a loss as to why the difference.

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

The problem was resolved by changing the data type in the table from Decimal to Double. The Decimal data type doesn't work very well in Access. For more info, see

http://www.allenbrowne.com/bug-08.html.

My thanks to Duane Hookom and Allen Browne for their help with this.