help needed with function

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hi all, I am creating a form in access from a table.
The form has the following information:


Deduction type 1 Deduction amount 1
Deduction type 2 Deduction amount 2
Deduction Type 3 Deduction amount 3

It also is set up to be one record- per purchase order.
The deduction types are combo boxes with drop down lists which are working fine.

I have a another field called total deductions.
I entered the following formula:=Sum([Deduction 1 Amount]+[Deduction 2 amount]+[Deduction 3 Amount])

Its working perfect except its adding every single value togeather for every record. Its not adding just the 3 deduction amounts per record.
Can someone please give me some advice? I;m kinda new to accessbut a quick learner... Thanks!!!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Remove the Sum() from your formula. Sum() would be used in Excel, but works differently in Access.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Thanks!! Its working for the first record, But record 2 and so are have nothing is the total deduction fields... Any ideas what i;m doing wrong?

Thanks!!!
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Never mind- i fiqured it out- Now here's another problem if you can help-
The total deduction amount is not being added into my table...

Thanks so much for your help!!!!
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

I will assume you have some deduction amounts that are blank. Sorry I did not include this in the first reply. You need to use the Nz() function. This function returns a zero "0" if you are dealing with a math problem, or a zero length string if dealing with string data. (Look it up in help to read all about it!)
So your formula would now look like this:
Code:
=Nz([Deduction 1 Amount])+Nz([Deduction 2 amount])+Nz([Deduction 3 Amount])
The blank field is a Null, and any time you add a Null, your answer will be a Null, therefore blank.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

Generally it is not a good idea to keep totals in your tables. If they are something that can be calculated again, which in this case they can, then you would best be calculating them each time the total is needed.
But, if you must have the total in your table, we need to know what are you doing on your form to try to get this total into your table.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hello again-
Well to sum it up i'm slowly trying to create a master database- which will track all of my vendors's problems and deductions.
This is still in its very early stages.
Part of this is creating the form which i;m going to use to enter the information for each of our purchase orders and any problems that occur. I was doing this via a spreadsheet in exell by each vendor and it was becomming a nightmare plus it was impossible to run reports..
Anyways
I want to be able to have all of this information stored as a reference and Eventually i want to be able to run reports by each vendor that will detail how many deductions they had during a certin time frame and what type of deductions they were.... Its hard to explain... and i;m sure i will eventually want to add even more.. Right now i have close to 40 spreadsheets with various information that i have to use to create pivot charts and lookup information.. I think by converting everything into access it will save me more time, and allow me to link more features and spreadsheets by vendor...
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Sounds like a very ambitious project. There are some basics that it would be good for you to know before going too far into Access. Please read the very helpful information in this thread.
 

Forum statistics

Threads
1,141,626
Messages
5,707,486
Members
421,510
Latest member
haroonstr

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