Calculating fields on form

[skydiver]

Access 2003. I've got a form based on a related table. I converted a field on the form to now begin calculating figures automatically from four separate fields. (I created this after the fact). The calculation of those figures adds up, however the total is not being displayed on the table datasheet view. I would like for this total to show up on my table. Help!

 

[boblarson]

You should not be storing the calculated values. You use a QUERY with those calculations in it and then you use that QUERY in place of the table wherever you would have used that table.

 

[skydiver]

Yo Bob! This is my dilemma. Our users inputting these figures CAN'T ADD, so I'm trying to automate this field for them. I already have various queries/reports that have calculations in place. It's my main table/form that the data is entered in manually. I have to rely on the users adding correctly in order to pull accurate reports. Unfortunately, Arithmetic is an issue here. Any suggestions?

 

[boblarson]

Yo Bob! This is my dilemma. Our users inputting these figures CAN'T ADD, so I'm trying to automate this field for them.

If the fields have the data, you do not store the calculation. You build it in the query and base everything else on that query.

I already have various queries/reports that have calculations in place. It's my main table/form that the data is entered in manually. I have to rely on the users adding correctly in order to pull accurate reports. Unfortunately, Arithmetic is an issue here. Any suggestions?

My suggestion still stands. You build a query which does the calculations based on the information entered in the form and you base the form and or other forms or reports that need that information on that query.

 

[missinglinq]

Bob's suggestion is right on the money, here, and you shouldn't be afraid of taking this route! Creating a Query based on your Table can easily be done using the Query Wizard.

You can then go into Query Design View and create a calculated field, using something like this:

CalculatedField: Nz([Field1])+Nz([Field2])+Nz([Field3])

Once this is done, simply go into Design View of each Form/Report, etc and change the Record Source from the Table to the corresponding Query.

Linq ;0)>