CODEWITHSUNDEEP
ms-accessreportms-access-2013calculation
StarsFlyFree FromCozyNights
StarsFlyFree FromCozyNights

Reputation: 303

Add Calculation Field To Report

I am running access 2013, with a linked view from SQL Server 2008 R2. I have two fields that I want to sum and data types are:

SQL Server 2008: Decimal(10,2)
Access: Number

They are being formatted in my query to currency using this syntax:

cf1: Format([calcfield1],"Currency")
cf2: Format([calcfield2],"Currency")

I have a report with both of these fields bound to text boxes and am adding a 3rd textbox to SUM() them. This is the control source syntax I input to SUM() the fields:

Name: SumOfFields
Control Source: =Sum([cf1]+[cf2])

Now when I attempt to view the access 2013 form, I get the error message displayed below. What do I need to change in order to perform this calculation on my report? Error Message

EDIT
If it makes a difference one cf1 is in the Serial Header section of my report, and the field cf2 is in the Details section of my report, and can have multiple entries. I want the SUM() that I am trying to add to be added to the Details section of the report like below:

Serial Header
$22.40
Details
$10.00
$13.40
$10.20

$56.00

Upvotes: 2

Views: 149

Answers (2)

Skippy
Skippy

Reputation: 1590

I agree with @user2676140 - the total should appear in the group footer and should probably look something like:

=[cf1] + Sum([cf2])

If this doesn't work then I would try taking the Format wrapper off [cf1] and [cf2] and use the controls' properties to format them as Currency. Then maybe have the ControlSource for SumOfFields as

=[calcfield1] + Sum([calcfield2])

Again, use the properties of SumOfField to set the format.

Upvotes: 1

Bob Goblin
Bob Goblin

Reputation: 1269

Check out the link here it may help. A few things come to mind to check, but with the limited info this is the best I can do...

1) One of the fields is in a custom header which would mean that the total you are attempting to add needs to be in the custom footer
2) You need to place the total in the footer of the report

Link

Upvotes: 1

Related Questions