Calculations in forms...

[usfscoop]

Hi Everyone:

I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.

Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.

I appreciate any and all advice.

Thanks!
Darin

 

[MStCyr]

It is not recommended that you store a value that can be calculated.

The value of Value 3 may be calculated as follows:

NZ( [Value 1],0) + NZ( [Value 2],0)

 

[Robert88]

Hi usfscoop,

Doh! I made a form with button which calculates the third field attached, but it is not what you want. Be back soon with a better sample.

Robert88

 

[Robert88]

Hi usfscoop,

Attached is a table, tblValues and query, qryValueTable and form frmSumValue1SumValue2 which includes Value1, Value2 & Value3.

As indicated by MStCyr, it is not a good idea to store calculated values. The form contains a query which within it are the calulated values as seen on the form.

Hope this is sufficient.

Robert88

 

[usfscoop]

Hi Guys:

Thanks for the advice! MStCyr, when I try the equation: NZ( [Value 1],0) + NZ( [Value 2],0), and I input the values in the field, the third field just "clumps the figures together. For example: If I do the following: Value 1: 12, Value 2: 13, then Then the third field will give me 1213 as my results. It doesn't add them, just puts the numbers together. Any advice?

Robert88 I am going to try your solution as well and see which one work best.

Now is there any way, at all that these calculations can be stored once populated? I know this isn't recommended, but I am open to any suggestions.

Thanks!

Darin

 

[WindSailor]

Try checking your Data Type for your 'Value1' and your 'Value2' in your table.
Make sure it is a Number and if it has a decimal in use (Your values that you are working with) then on the General tab, change the 'Field size' to Double.

For database structure - Normalization - take a look at this and make a couple of pots of coffee:
http://www.access-programmers.co.uk/forums/showthread.php?t=100211

I used to store certain values, grant you not all, and some people still do especially when the calculated sum will NOT change over time.
But... it is not a good idea and it will be very hard to change your habits later especially if your application works and has been working fine for years.

It is merely a 'Fail Safe' for Murphy's Law, and it simply works.

Edit--------

Trying to be totally correct...
There are times when you probably need to store a calculated value, for instance a price for an object that you sold to someone at a particular time.
You would probably need to save that value because the price of that object would change over time...
Anyway take a look at the above link.

 

[MStCyr]

Hi Guys:

Thanks for the advice! MStCyr, when I try the equation: NZ( [Value 1],0) + NZ( [Value 2],0), and I input the values in the field, the third field just "clumps the figures together. For example: If I do the following: Value 1: 12, Value 2: 13, then Then the third field will give me 1213 as my results. It doesn't add them, just puts the numbers together. Any advice?

Robert88 I am going to try your solution as well and see which one work best.

Now is there any way, at all that these calculations can be stored once populated? I know this isn't recommended, but I am open to any suggestions.

Thanks!

Darin

Here's an example to show that the NZ function does work.

 

[usfscoop]

You were right! The format was put in text not numbers. Thanks Now I just need to work on figuring out a logical system for storing the calculated values.

 

[WindSailor]

Now I just need to work on figuring out a logical system for storing the calculated values.

Why do you want to store the calculated values?

Edit-----

One problem with storing calculated values is that you have no history on how you came to that value, unless you also store the components of the equation. And if you do that then why store it?
Also if you don't store your components, then you can not validate your value.
Give it some thought.

 

[MStCyr]

You were right! The format was put in text not numbers. Thanks Now I just need to work on figuring out a logical system for storing the calculated values.

Although not recommended, here's a way you can store the values.