calculations

fqb34r

Board Regular
Joined
Nov 28, 2005
Messages
71
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 12, 2009
Messages
292
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 28, 2005
Messages
71

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 28, 2005
Messages
71

ADVERTISEMENT

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

kev
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 28, 2005
Messages
71
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top