calculations

[fqb34r]

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

 

[Joe4]

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.

 

[gameover]

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?

 

[Joe4]

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])

 

[fqb34r]

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

 

[Joe4]

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.

 

[fqb34r]

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

kev

 

[Joe4]

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).

 

[fqb34r]

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

 

[Joe4]

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