Help with Sum equation in forms

[Buckles]

I have tried using the "Expression Builder" =[sample]+[sample]+[sample]+[sample] and get a blank text box.
I've tried placing the equation in an "=SUM([sample])" and it does not work. I get the #error sign.
The box is unbound and all the sample boxes have been unbound.


Any suggestions?

[June7]

Aggregate functions must reference a field. If you are using this in a textbox on form, the form must be bound to dataset and the expression references field, cannot reference control name.

What do you mean by 'all the sample boxes' - why is there more than one and why are they unbound?

[Buckles]

I originally set up many fields such describing car accessories such as Air front, air back, power window, roof rack etc. and assign currency in the properties. Both boxes had the same field name.
I then went to the tools box and entered a new text box to create the Total value of the accessories. This newly added text box was unbounded. I entered the equation to obtain the sum of all the accessories. I tried it with field name. I tried it with unbounded boxes, I tried it by entering the equation in the sum equation. I tried it using the expression builder and nothing seems to work.
I used the word "Sample" to describe the many accessory fields. I was able to get it to work with only the sum of 3 fields, but when I tried to get the sum of 14 fields, I ran into trouble. Perhaps if you were to give me the steps to take, I could follow them.

[June7]

Arithmetic with Null results in Null. Handle possible Null with Nz() function

=Nz(field1) + Nz(field2) + Nz(field3) ...

Aggregate function is to calculate multiple records, not multiple fields.

Your table structure is not normalized and that is the root of issue. Consider:

tblVehicle
VehicleID
VehicleName

tblAccessories
AccessoryID
AccessoryName
Price

tblVehicleAccessories
VehicleID
AccessoryID

Now can do an aggregate query to Sum the price field. Build a query that joins tblVehicle and tblAccessories to tblVehicleAccessories, pull fields to the grid, then click Totals button on ribbon, select Sum under the Price field.

[Buckles]

I'm not sure of what you said. I'm new to this and have limited ability with access. I'm trying not to create different tables. I have everything and would like all my data on one form as it is now. Please see image of form

[orange]

I'm trying not to create different tables.

This is not necessarily a reasonable design strategy with relational database.
Much better, and easier, is to get a clear, concise description of the business problem/opportunity; create a data model; vet the model against your requirements using test data and scenarios; then build the database.
You seem to have done a lot of work to create your Access materials.
Perhaps you can post your database with some sample data and
an example of exactly what you want along with the original inputs.

Good luck with your project.

[June7]

Trying to get everything in one table is a 'spreadsheet paradigm', not utilizing relational database concepts. It can be dealt with but is not optimum design. Relational structure would use form/subform arrangement.

I have already suggested one technique to manipulate data by use of Nz() function. However, this expression for 14 fields might exceed allowed length which might be 255 characters. Give textboxes very short names (like A1, A2, etc.) and build the expression by referencing those instead of the longer field names.

Still don't know why 'sample' boxes are UNBOUND.

[Buckles]

What you have said is what I'm experiencing. I can perform calculations with several records, but when I try to calculate them all, I discover it doesn't work. It is important for me to use the names of accessories to be able to recognize it. To use A1, A2 would cause me to make errors. If this won't work with a Form, I may have to go to a Spread Sheet like Excel. I was hoping to have all my data for each vehicle on one Form that would also update the inventory number for each vehicle. Is possible for me to leave the Accessory names the same and add a small cost record next to it and call it C1, C2 ETC? I'll try this out.

[orange]

I recommend you work through one/some of the tutorials form RogersAccessLibrary. You will learn about table design, normalization and relationships ---all key database concepts.
In my view, you are working in "spreadsheet mode", and that won't serve you well for database.
Good luck.

[Buckles]

Thanks for your help. I'll check out the tutorials. Coudln't find any way to say this message is solved or I would have marked it solved

[June7]

You aren't calculating 'several records', you are doing arithmetic with several fields of same record.

Why would naming textboxes like A1, A2 be any more confusing than cell referencing in Excel?

The field names can remain as you have them. My suggestion was for naming the textboxes and using those short names in the expression to hopefully stay under the character limit. Otherwise, will need VBA code to do the arithmetic.

Thread Tools dropdown above first post.