% Calculation (1 Viewer)

[Puddy]

Very simple question here...just haven't worked with VB for a few months and am forgetting a few things...my scenario is that I have a %Defect field that I want to have calculate the AmtDefect/QuantSuply fields....I want this to happen AfterUpdate on my AmtDefect field...I just forget what I should do.. Any help would be greatly appreciated...thanks

 

[jwindon]

Me.%Defect = Me.AmtDefect/Me.QuantSply

You may need to expand on the equation depending on the data types of these two fields. You didn't say.

You might want to note that storing a calculated value is "against the rules". Store the other two fields and make &Defect unbound on your form.

 

[Puddy]

How would I go about saving that % field then in my table? Is there a vb code that will do this for me.

Thanks

 

[jwindon]

You should not need to store the percentage. You can recreate that equation on any place that you need to show it.

In a query, you would type Percent: [AmtDefect]/QuantSuply] in the field name on the query grid.

In a report, an unbound control with the equation in it.

The point is that you can reproduce the results whenever you need.

If you absolutely have to store the percentage, you can set the control source for a percent field on your form (which is bound to the underlying table) to the equation. Do this in the properties of the field on the data tab.

 

[KKilfoil]

The big downside to storing calculated data in a table is that you will forever thereafter have to ensure that the calculated values are ALWAYS up-to-date. Users must not be allowed to modify the data in the table without using your custom forms, etc. You also waste a bit of memory.

If you are going to use a calculated field a lot (I define 'a lot' as more than one form or report!), you could create a query from your table(s) that contain AmtDefect and QuantSuply.

In this query, add a calculated field, like:
%Defect: = [Amtdefect] / [QuantSuply]
You might as well format this field as a percent while you are at it.

Now use this query as datasource anywhere you need %Defect.

You could also add the rest of the fields from your original table, and then use the query exclusively where you used to use the table. This is what I tend to do, so long as the query calculations don't add a lot of time overhead to my forms or reports.