Calcuating Values

[WhatnThe]

Is is possible to calculate in my data entry form before adding the new records.



I am using look up values to get most of my data, but one of the last things I need to is calculate the total number produced by multiplying values in my form. When doing these types of calculating values is it the same as excel.

To get my ppr I would take the Lth x P x SP so in my PPR field how could I calculate this.

[jbarrum]

Calculated fields are not stored in the db beause they take up un necessary space. If you need to make a calulation it can be done one of two ways. You can either build a query and use the last column in your query to write the formula (otherwise known as an expression. Or, you can build the same expression in a control on the form. If the formula that you indicated above represents fields in your table that the form is based on the the expression would apear as follows: [Lth]*[P]*[SP] where the letters in the brackets represent the field names.

[Rainlover]

<< Calculated fields are not stored in the db beause they take up un necessary space. >>

This is not strictly correct.

Once upon a time space was a consideration, but not so important these days.

The main reason calculated values are not stored is because the fields which were used in the calculation may have their value altered, hence the calculated value becomes incorrect. It is therefore advised to calculate at runtime.

There are however some situations where storing calculated values a must.

It is up to the busines rules to decide on which method should be employed.

[WhatnThe]

I did figure out how to calculate values within my form witch is what I was looking to do. Its the values that I want stored in my data entry table. Now my issue is in one of my fields I am calculating the pieces produced using this =[Legenths]*[PPL] and I do get the correct answer but, when I add the record that value is not added to my data entry table.

I am still not sure why.

[RuralGuy]

You will probably benefit from this link.
http://allenbrowne.com/casu-14.html

[WhatnThe]

Not sure its going to do what I want. I want to calculate the value of Pieces Produced within my form based on the value from two other fields within my form. I don't need to store the formula in my data entry table just the value that formula produced.

So I guess I need to know if I am going in the wrong direction here. Is it not possible to place a formula in one of my form fields and have the number produced be recorded in my table?

[RuralGuy]

You need to read all of the link I supplied. The answer to your question is there.

[Rainlover]

RG is correct. The answer is in the Link he provided.

Are you still having problems?

[WhatnThe]

Yes, I am trying to follow the information in the link, but I think I am having a brain fart here.

I am going to upload a demo of my database the way I have had it set when I first asked the question. When you pick a number from the combo box you can see that we have a default PPL automatically populate the PPL field. As you fill in the Lengths field you will see the we get the total Pieces Produced We do get an accurate number but the "Pieces Produced" using =Lengths*PPL

When you look at the data entry table you can see all other info had been added but the value produced from Pieces Produced. Sorry this may be end up being a stupid question.

[Rainlover]

Sorry but I do not have 2007.

Can you convert to 2003 and reattach.

[WhatnThe]

I just tried to save it in 2003 but My form is missing from the database now. What should I do?

[WhatnThe]

I am going to recreate the form I will upload it in a few minutes.

[WhatnThe]

I form in now listed under the data entry table. Let me know if this is what you need to view the demo

[Rainlover]

I don't know 2007 so try looking under the All Tab. It should be there somewhere.

[WhatnThe]

I did find what you added to the database. I am now working on getting that value that is being recorded to the database to display to the form field