Formula

[mtriolo]

Hi

I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.

I have tried using the iif formula without success. Here is what I want to calculate.

I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.

If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....

If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5

Can anyone help me with this formula.

 

[boblarson]

You probably shouldn't be storing it as you normally shouldn't store calculated values which can be recreated at any time. You can create a function to convert it easily enough and then display at any time.

 

[boblarson]

Public Function ConvertGPA(dblValue1 As Double, dblValue2 As Double) As Double
    Dim dblDiff As Double

    dblDiff = dblValue2 - dblValue1

    Select Case dblDiff
    Case 0

    Case 0.05
        ConvertGPA = 3.5
    Case 1
        ConvertGPA = 3
    Case 1.5
        ConvertGPA = 2.5
    Case 2
        ConvertGPA = 2
    Case 2.5
        ConvertGPA = 1.5
    End Select

End Function

Then, in a query you can use

GPA:Format(ConvertGPA([FieldScore],[BaseLine]),"0.0")

 

[mtriolo]

Thanks for the reply - the VBA formula that you gave me do you create this in the modules?

I will try thanks again for the help

 

[boblarson]

Yes, put it in a STANDARD, not form, module.