Summing numbers

[Geno]

Although I have been a heavy user of Access I am new to design. I have a simple table "Maintenance" consisting of Parts, Labor and Taxes. I have a form for inputting values and have a "Total" field which is sum of parts, labor and taxes =[Parts]+[Labor]+[Tax]. The formula works fine. However I want a Total Maintenance field which will reflect total maintenance costs. I used sum("[Total]") and got a total but when new records are added the total maintenance does not change.

Any form designed can be based on a table or query, I have no preference but when trying a formula that works I have not been successful. Any help is appreciated.


Gene

[NTC]

Your post is not clear. Do you want a Total Maintenance Field? if so, add that new field to your table.. Or do you want a Total Maintenance Calculation displaying in an unbound control? ...if so what is the formula?

[June7]

Where is the expression Sum("[Total]")? I don't see how you could get any result with that. There should not be any quote marks in that expression. Even without the quote marks it won't work because Total is not a field in the form's RecordSource, it is just a textbox with a calc. Do the [Parts]+[Labor]+[Tax] calc in a query that is the form's RecordSource (or in textbox =Sum([Parts]+[Labor]+[Tax]) point is, aggregate functions must refer to actual fields). Then the Sum() function can reference the constructed field. Also the Sum function would have to be in textbox in form header or footer section which means the form needs to be in Continuous View. There is a method to show Sum() calc in a Datasheet view form. Open the form and click Totals button on the ribbon. This will open a Totals row at bottom of the form. Select Sum under the Total field.

[Geno]

I am looking for a Total Maintenance Calculation that will give me the total maintenace costs to date

[June7]

Did you try suggestions?

[Geno]

Suggestions to date have not solved problem.

[June7]

Then I don't understand what you want because the suggestions I offer incorporate the calculation you identified in your post.

[Geno]

I have a form with 3 fields "Parts", "Labor" and "Tax". These are taken from a table. I have a form for entering numbers in these fields. The form also has a calculated field "Total" which is the sum of the 3 fields calculated by the formula =[Parts]+[Labor]+[Tax]. I want to know what the total value is of all the calculated fields "Total". The form was created from a table not a query. I don't know if a query would be better used to create the form.

[June7]

Then my suggestion for calc =Sum([Parts]+[Labor]+[Tax]) should work. However, if any of those fields is Null, the individual record result will be Null. Arithmetic with Null returns Null. Handle possible Null with:

=Sum(Nz([Parts],0)+Nz([Labor],0)+Nz([Tax],0))

If you want to provide db for analysis, follow instructions at bottom of my post.

[Geno]

Problem solved. Thanks