calculations

fqb34r

Board Regular
Hi

I have 8 fields that i need to add up to find the total amount now its fine when all 8 fields have an amount in them but one is missing nothing adds up can someone help me with this problem

What i have done is in my form in the field total clamed I have entered the following in the control source box =[cost]+[cost1]+[cost2]+[cost3]

Kevin

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Yes, NULLs can mess up you math. Try using the NZ function, i.e.

TotalCost: NZ([cost],0)+NZ([cost1],0)+NZ([cost2],0)+NZ([cost3],0)

The second argument of NZ tells it what to use in lieu of a Null value.

Hi,I have got a problem related to this question. When I use operator + what it does is put the nos. sequentially. e.g. if 1st box has got 12 and 2nd has got 2 it doesnt add upto 14 instead the result comes out to be like "122". I have tried subtracting, multiplication etc. and it works fine. Can you please tell me whats the problem?

Are the value you are adding stored as Text or Numeric? Sounds like they may be Text, in which case you might need to convert them to values first.

The reason why it works for other function, like subtraction and multiplication is because Access knows that those are arithmetic functions and automatically "coerces" the Text values to Numeric. However, the plus sign ("+") can mean arithmetic addition OR text concatenation. If your values are stored as Text, it will do the concatenation to them.

So, either change the format of your fields to numeric, or use the VAL function in your equation, i.e.:

Val(Field1)+Val(Field2])

Hi

I have entered this in the control source box in my form but this must be the wrong thing to do becouse the data is not returned to the table i tryed it in the table but that didnt seem to work where should i enter the formula

kev

Is this TextBox on your Form bound to a field in your Table?

If so, what I would do is changed the Format of the Field in your Table (from Text to Numeric). Then, go into your Form, remove the current TextBox and then create a new one from your Table Field (click and drag from the Field List Box). Then this new one should have all the same properties as the Field in your Table.

I tryed that but by changing the controle sorce it seem to stop the data from entering in the table

kev

OK. I think I may be confusing a few issues here (Gameover's request with yours - the whole Text to Numeric thing).

In order to really determine what is going on, I think we need to know a bit more about your set-up.

Can you list all the Form fields involved in this problem, and what the Control Source of each is? Any maybe give a simple example.

By the way, you should not be trying to store a calculated field back to a table. Anything that can be calculated can be done so in a query, and not stored in a table (I am not sure if that is what you are trying to do).

yes i am trying to return a calculated field back to a table and i take it that cant be done

i have 8 cost field and a total clamed field which i was trying to add up automaticly in my form so when i run my reports which is basicly a clame form the totals will be there

i think now i should do my calculation in my report insted of my form

kev

There is another way to do this.

Do this in a query (that is where I thought you were entering your calculation in initially), and then use this Query as the Source of your Form instead of the Table.

You could also calculate it in an unbound Text Box on a Form or Report (like you mentioned). Since this total can always be calculated from the other values, there is no need to store the total.

As a general programming rule (per rules of database normalization), you should never store a value which can be derived/calculated from other field. To do so can ruin the dynamic nature of the database and compromise the integrity of the database.

For rules of database normalization, check out this link below. Most designers will try to get their database in at least 3rd normal form:
http://datamodel.org/NormalizationRules.html

Replies
9
Views
451
Replies
13
Views
579
Replies
1
Views
428
Replies
1
Views
498
Replies
1
Views
380

1,211,987
Messages
6,105,227
Members
447,957
Latest member
Basildon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back