Sum Not Working

I have had some basic training on access 2003 the rest is self taught.
I used this forum last year to help generate a stock figure on a report from a form/subform.
I have now created forms to make entering the stock transactions easier for users but the stock figure on the report now generates a figure for each individual form entry
(Stock form/subform is still OK)
Stock transactions for 30/04/10 will give a Total for Stock Item 1
Stock transactions for 01/05/10 will give another Total for Stock Item 1
Instead of 1 Total for all seperate date entries
Expression on both form and report (Query)is
Stock Total = Sum(nz([Qty-Rec,d])+nz([Qty-Return])-nz([Qty-Issued

Sponsored Links:

Related Topics

Totals and Sum not working.
This currently affects all my objects, from table up to forms. My problem is that I cannot make the Totals row on the datasheet view to work, nor can I make the form footer sum to work.

Usually, I just sum a field at the footer of the subform with a textbox with a value of =Sum([Field]). Then I call that particular textbox on my mainform with =SubFormName.Form!ControlName. I have several of this controls already working. Each one assigned to a different subform, all of them working.

But when I did the same thing to another subform, I get a #Error in my totals textbox on the footer of my subform, it won't work anymore. So I checked and re-checked my syntax, they are all correct.

To check, I pressed the "Totals Row" feature of access 2010 in datasheet view, but could not make the sum value appear. however, the average, min, max, variance, deviation, etc. are all WORKING except for the "SUM."

VBA code to sum highlighted
Working in Access 2003. In a tbl or in a qry I must sum or count the highlighted fields like in the attached clip (count=3 Sum=24) .

Sum function on report not working
I have a report created where the page uses a query as the back end, all works well. I need to add a text box with the summed up values; however, I keep getting the #error for the result of my sum function.

I am trying to Sum up one field called SCHDQTY. On the textbox I am trying to put the total into, I have this as the control source:


and I get the #Error message when I go into preview view

How do I sum multiple sums?
I've been searching and trying this for quite a while, but can't quite get a sum of all my sums.

I have a report with about 15 subreports, they all contain different cost fields, ie one is ActualCost, another is DiscountedCost, etc. Each cost field is summed in each subreports footer I.e. "=Sum(ActualCost)"

How can I get a Grand total of all the sums from all the subreports? and I don't want that to screw up the formula.

I've tried things like =Sum(ActualCost) (DiscountedCost) and =Sum(ActualCost) Sum(DiscountedCost) But nothing like that is working for me, it just asks for an input and the diplays the random number I type or Error.

SUM each individual column
I am trying to get the total in each column but it doesn't seem to be working?

The SUM of 004 should be 3808 but it comes up as 82844?
The SUM of 008 should be 88 etc.

Then the numbers seem to be adding? As the result in each column is getting bigger and bigger

This is my SQL:


This is the Result:

Calculating overtime in report footer
I currently use the following formula in a text box in the employee footer on my report to calculate the amount paid to each employee is paid:

=([On Std Hours Sum] [Off Std Hours Sum] IIf([On Std Hours Sum] [Off Std
Hours Sum]40,0.5*([On Std Hours Sum] [On Std Hours Sum]-40),0))*[Current Pay Rate]

I should note that the [On Std Hours Sum] and [Off Std Hours Sum] are both sums (if you didn't already catch that!) Now, I need a sum of that formula in the report footer but as you know you can't sum a sum. I attempted to use the totals function inside the query that I have based this report on to sum the On and Off Std Hours but it only shows the last entry for each employee. Any ideas on how I can sum the above code properly?

How do I show 0 instead of #Num! errors on my report
report building, I have basically self taught myself and need some help with showing zero's instead of those ugly errors. #Num! and #Div/0!

The calculations from the three boxes on my report that are bringing up this error are below.

=(Sum([New Appt])/2)/(Sum([Contact]))

=Sum([Number of Sales])/(Sum([Appt1]/2)+Sum([Appt2])/2)

=(Sum([Sale/ renewal/ review])*100)/(Sum([number of sales

The Sum procedure is not working in macro
Why is not working the sum procedure in macro.Where am I doing mistake. Could you please help me.

My example in attach

Sum(Round) gives incorrect value.
I have along list of values of which I need to sum together. The issue I have is, with the list (sorry in advance):

I use SUM(Round(value,6)) and get the result "1.19371179607697E-12". What I expect, and what has been worked out in Excell / calculator is zero. I believe that Access 97 has some mathematical issues with decimal sums? Is there a way to get the desired result?

The field type on the value is (Double, Fixed, 6dcp). I was working on this yesterday with 1dcp and was fine with smaller values

Sub totals in a report
I want to arrange my data so that it is sorted by month, What is the Formula to make it sort by month, and the correct formula to put in design view in the detail or footer for the subtotals and totals?


SUBTOTAL =(Subtotal[SQ FT]) =(Subtotal[Sum])


SUBTOTAL =(Subtotal[SQ FT]) =(Subtotal[Sum])

GRAND TOTAL =(Sum[SQ FT]) =Sum[Sum])

Having a Total sum of Grades
I'm working now with a simple database for students and I have a little bit problem for you to ask and I know that someone here in forum knows it. In my form I have fields of student's Subject with Corresponding Units. My Question is, Is it possible to sum up Automatically the Units for all the Subjects using Expression Builder ONLY

Sum a field on in a report not working
I have a field on a report footer that I want to dispaly the totals in. In the control source I have this code

=Sum(Nz([Number of Children],0))

If the report has a blank value it errors out. How do I get it to
ignor blanks or see then as

Can't Sum On the Report Footer
Can't Sum On the Report Footer. In the detail I have Unbound Text-box "LossMoney" With Expression =Nz([Price])*Nz([Qt3]) working ok giving the correct results.
Now at the footer of the report I want to sum the "LossMoney"
Tried many variants, but not worked

Receiving an #Error message in a form footer sub total
I have a customer database that I use and want to create quotes from; however, the form footer sum function is not working. I need to have it multiple the size by price by qty.

Both the price and qty field are text boxes but the size field is a lookup field. I understand that the sum function is not able to use calculated fields. Is there away around this to make my sum work?

Subform Foot Sum Resulting in #Error
I had a working form but then my client asked for the form to be changed and with those changes my subform is not summing the totals in the footer properly. It is giving me a #Error message. My line total is working fine with the formula =([Size]*[Price])*[Qty] but when I enter =Sum(([Size]*[Price])*[Qty]) it is giving me the error.

I have included a Access 07 demo database to better view the issue.

Executing a query that does not include the specified expression...
I originally thought this problem was with my report so I posted it under the reports section but not I'm seeing that it is a query problem so I am re- posting it here. I ran in to some problems while trying to do a sum in my report footer but had problems because I was trying to do a Sum of a Sum. So, I created an expression in the query that my report is based on to do the Sum for me. However, when I add the totals option to the query and select sum underneath the On and Off Std Hours, it doesn't sum it up for me in the report. It just shows the last entry. So, I tried to manually create it by placing the following in a new field:

Can hours be sum?
I am trying to total hours worked for the week.

Problem1: group by employee, and sum the hours for each employee per day.

The fields are StartTime, EndTime, HrsWrkDay, hrsWrkweek, the format is 00:00

I have the grouping done but the sum of the hours are not correct.
So can I sum time? or do I have to convert the daily time total to something else and then sum the daily?

How to sum up hours
I am trying to total hours worked for the week.

Problem1: group by employee, and sum the hours for each employee per day.

The fields are StartTime, EndTime, HrsWrkDay, hrsWrkweek, the format is 00:00

I have the grouping done but the sum of the hours are not correct. So can I sum time? or do I have to convert the daily time total to something else and then sum the daily?

Calculated Fields in a Report
I am trying to add a sum on a report. It is a report with six columns, aging report: 0-30, 31-60,61-90,91-120, 121+ Open Invoice Amount.

=Sum([Open Invoice Amount]) is adding correctly. I am adding the rest of the buckets, =Sum([121+ Days]), =Sum([91-120 Days]), =Sum([61-90 Days)], =Sum([31-60 Days]), =Sum([0-30 Days]) but I get this error:

"This expression is typed incorrectly, or it is too complex to be evaluated."

How To Calculate A Subtotal In A Continuous Subform
here. I'm relatively new at the whole Access game, but am working on an ordering guide for my work. The part that I'm stuck on, is that I have Form with client contact information. Within that form, there is a Subform with the order information. The Subform is continuous. Now in the footer, I have a subtotal textbox which isn't updating. Here's what I've tried so far:
None of these seem to work. Is what I'm trying to do even possible