Trouble with Sum Function

[MikeKohler]

Hi, I have a subform within a form that shows part number, price and price plus tax for each work order. This subform is a part of a larger work order form. For each work order a certain part or parts will be used and I wish to show this total in the main form. I have the subform set up as datasheet view, and the main view as tabular. I did not store the calculations in any field, I have read that it is better to calculate everything on each form or report.
However I am getting an error with the sum function, when I try other calcutaltions they work, but with sum, #Error is what shows up as the result.
Any help would be really appreciated, Thank you,
Michael Kohler
mkohler@telusplanet.net

 

[BoxHead]

Michael,

Are you trying to sum calculated fields? This is out of MS Help.

When computing a total with an aggregate function such as Sum or a domain aggregate function such as DSum, you can't use the name of a calculated control in the Sum function. You must repeat the expression in the calculated control. For example:
=Sum([Quantity]*[UnitPrice])
However, if you have a calculated field in an underlying query, for example,
=ExtendedPrice: [Quantity]*[UnitPrice]

you can use the name of that field in the Sum function, as shown here:
=Sum([ExtendedPrice])

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[MikeKohler]

Thanks for the response, I have been been repeating the expression in the sum function. But even when I enter simply a field, one which is not calcultaed, I still get the error message.
Michael Kohler

 

[BoxHead]

Michael,

Two things to try/check.

Spell out the full name of your fields.

   Sum([Forms]![frmWhatever]![fldWhatever])

Make sure the fields you're trying to sum are actually numbers. Look at the CInt function.

   =Sum(CInt([Quantity])*CInt([UnitPrice]))

HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[MichaelRed]

Sum? Some functions are SQL aggrerate functions -treated as SQK Key Words within Ms. A. so any 'calculation' on a form would either need to be a custom function (NOT!!!! named "Sum&quot, or just an atith assignment.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BoxHead]

MichaelRed,

I don't understand. I have a form based on a table called tblSales. One of the fields in the table is SaleAmount.

On the form, I placed an unbound textbox and typed

       =Sum([SaleAmount])

into the control source for the text box.

It returned the total (the sum) of the SaleAmount fields.

I gathered from your post that this wouldn't work.


Michael K,

I also used

     =Sum([saleamount])*[saleid]

and returned the appropriate calculated values. I deleted the SaleAmount textbox and the SaleID textbox trying to recreate the error but it worked just fine.

Have you tried the CInt in case any of your values are being read as strings? Could there be a missing reference from the main form to the subform? Could I clutch at any more straws?


Take care,

John

Use what you have,
Learn what you can,
Create what you need.

 

[MichaelRed]

It "Works" here (I think) because the Form itself is bound and the control (field) is part of the RecordSource/COntrol Source - so, Ms. A CAN figure it out.

AFAIK, the earlier POST is true. BNUT- there are many 'oddities' in the old lady.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BoxHead]

MichaelR,

Right you are. On an unbound form, DSum is necessary to reference the domain/table.

Michael K,

Did you ever get this solved? John

Use what you have,
Learn what you can,
Create what you need.

 

[MikeKohler]

Thanks for the responses, I am bit confused, if I create an expression for an unbound box, does this mean that agregate functions won't work and that I have to put this calculated value in a field. I created my forms using the wizard, so they should be bound to a table, shouldn't they? What I am thinking is that I should create a field for this value and then use queries to calculate the value for table.
Michael Kohler

 

[BoxHead]

Michael,

As long as the form is bound to the table that includes the fields you're calculating, you should be alright. In my tests, the textbox wasn't bound, it was a calculated control with

 = Sum([SaleAmount])

as the RecordSource.

If the field you're calculating is in another table, you should be able to refer to that field with DLookUp.

Lightbulb! This is where we're getting twisted around. The Sum function returns the Sum of all values in the field. It does not return the Sum of control values. In other words, Sum cannot be used to add TextBox1 to TextBox2. That would have to be

TextBox1 + TextBox2.

Could you post the statement you're using and identify the components?


John

Use what you have,
Learn what you can,
Create what you need.

 

[MikeKohler]

ok, here is the formular I used for parts price,
=[Parts_Price]*[Quantity]*1.07
Parts_Price and Quantity are fields in two seperate tables. Parts_Price is a currency and Quantity is an integer. This was placed in a subform, where for each work order that someone enters they also enter the parts. Therefore, for each work order, they will also be a set of parts used. What I wanted to do was as each part is entered, a running total is shown in a text box beneath the subform for each Work Order.
What I liked about using text boxes for the calculations is that as each number was entered the calculation was done instantly, I didn't have to tie in any queries or create a command button.
Michael Kohler

 

[MikeKohler]

I have finally got it to work! What I did was to change the format of the parts subform from database sheet to tabular, I then calculated the sum within the subform, instead of the main form as I had it. For some reason this worked and I now get the total I was looking for in the text box.
Thanks,
Michael Kohler