Equation in a form in a specific field

[boltonea]

I have a form built from a table and in a specific cell I need an equation but can't figure it out yet. I have several different fields and I need 2 fields divided to get the result. i.e. Total_Actuals divided by SA_CDI_Money = MoneyPercentage. Any ideas how I can get this to work?

 

[reclusivemonkey]

=[Total_Actuals]/[SA_CDI_Money]

???

...if you need it as a percentage, simply multiply by 100 OR set the field to be a percentage.

 

[seth_belgium]

The way to calculate the value is this:

[Total_Actuals]/[SA_CDI_Money]

The question now is, when do you want this calculation to take place?

I would assume you would want to do it after both fields are filled in by the user. You could do something like this:

Use this code:

Private Sub Form_Load()
Call CalcMoneyPercentage
End Sub

Private Sub SA_CDI_Money_AfterUpdate()
Call CalcMoneyPercentage
End Sub

Private Sub Total_Actuals_AfterUpdate()
Call CalcMoneyPercentage
End Sub

Function CalcMoneyPercentage()
If Not IsNull(Me.Total_Actuals.Value) And Not IsNull(Me.SA_CDI_Money.Value) Then
    Me.MoneyPercentage.Value = Me.Total_Actuals.Value / Me.SA_CDI_Money.Value
Else
    'You can choose: You can show a text-value saying that there are missing value(s)
    'or just show nothing
    'Now it is set on showing a text-value...
    
    'Show text
        Me.MoneyPercentage.Value = "Missing Value(s)"
    
    'Show nothing
        'Me.MoneyPercentage.Value = ""
End If
End Function

If you don't understand it, just take a look at the example database in attachment.

Good Luck!

Greetz,

Seth

 

[boltonea]

Not sure if I understand. I have the Spotlight_Project table and the vwSpotLight_Project_Prcnt table. The vwSpotLight_Project_Prcnt table has columns ID, Total_Actuals, SA_CDI_Money and Percentage. This actually shows the figures. I have the Spotlight_Project form that was created and it has those same columns. In that form under Percentage, is that where I set the field as a percentage?

 

[seth_belgium]

Oh! We thought you actually wanted to calculate the value.

In that case, yes, on the form, that's where you set the field to percentage.