Populate an access field with results of calculation?

[jrpatdlgs]

Using a Form we are having trouble getting the results of a two-field calculation to become the contents of a third field in the access dB. Very simple: field_A plus field_B = result. This calculation is to be performed on every record in the database with the "result" of the calculation to become the contents of a third field (field_C)in each record.

We've looked at the book and at the help screens and somehow this very simple procedure remains beyond our grasp. Someone's help in pointing out what I am sure is something very simple that we are overlooking would be greatly appreciated. Thanks all.

 

[PaulF]

use this for the ControlSource of the 3rd textbox

=Val(nz([txtA],0))+Val(nz([txtB],0))

where txtA is for the textbox containing Field_A, txtB is the textbox containing Field_B

PaulF

 

[jfhewitt]

The suggestion will solve any problems will nulls, but is that your problem? If you want a field in the table to contain the result of the calculation, and be a separate, discrete field, you will have to use VBA to use the "AddNew" method to place the result of the calculation into the field, by storing the result to a variable and then adding the variable to the field. I know your confusion...usually a database has a simple method of populating fields with calculations...Access seems to feel that since you have the base data in the db, why do you need another field, if you can display the result in a text field by entering an expression. I hope this helps somewhat. Another method is to create a query and display your result in a query field, using your query for forms and reports.

 

[JerryDennison]

I strongly urge you not to attempt to store a calculated value in your db. This goes against good relational database design and can lead to more problems than you can imagine. It is far better to calculate on demand. By following PaulF's suggestion you will have created an unbound textbox on your form that will show the calculated result for each record as you browse through the table and will "update" automatically whenever data in either of the controls in the expression change. Should you desire, you can add the calculated control to the underlying recordset by adding it as a calculated field in the form's query. If you'll create a standalone query that includes all the fields from the table plus your calculated one, you can use this query as the foundation for other forms and reports that would have been based on the table alone. Doing so you'll always have the calculated field available for the form or report.

Please study the Forms of Data Normalization for more help in designing robust and sustainable database applications.

 

[Guest_imported]

Jerry Dennison is correct!! Other than his suggestions,the easiest way would be an update query. (I recommend calculating on demand; that is the way we do it.)

Andre S.