calculated field

[armin]

I created a field on a data access page that is supposed to calculate grade (A,B,C,D, or F) based on the data from another field on the same page. For example, the "Percent" field calculates and displays the score student got on his/her test as a percentage. Now I'm trying to make this "Grade" field that is going to automatically calculate what letter grade corresponds to the score from the "Percent" field.
I am new to Access so your help is appreciated.

Thanks

 

[Ally]

I’m not that hot on code, but this is something I did for calculating the “time-bands” that certain times fell into, so I’ve modified it for you which I just checked and it works.

Copy the following into a module. You'll obviously need to change the values to match the correct grades. I just made them up.

Function Grade(x)
    
    If x < 20 Then
        Grade = "F"
    
    ElseIf x < 35 Then
        Grade = "E"
        
    ElseIf x < 50 Then
        Grade = "D"
        
    ElseIf x < 70 Then
        Grade = "C"

    ElseIf x < 90 Then
        Grade = "B"

    ElseIf x <= 100 Then
        Grade = "A"

    ElseIf IsNull([x]) Or x = 0 Then
        Grade = "No Contact Time"

    End If


End Function

Then in a query, you need to put:

Grade: Grade([YourPercentField])

Add any other fields you need present.

(Obviously change the name of YourPercentField to the name of your percent field).

You can then display the results in a form or report.

HTH

 

[Rich]

I'd say a Select Case function would be better here

 

[Ally]

Yes - true, although doesn't Select Case have problems with IsNull?

 

[Rich]

I've never had one, there is a problem with the If that you've posted, you need two conditions, as posted anyone with less than 100 will get A grades
eg. ElseIf x < 35 Then
Grade = "E"
should be
ElseIf x >20 < 35 Then
Grade = "E"
here's a simple function
Function GetGrade(fldData) As String
Select Case fldData
Case 0
GetGrade = "No Marks"
Case 1 To 20
GetGrade = "F"
Case 21 To 30
GetGrade = "E"
Case 31 To 50
GetGrade = "D"
Case 51 To 60
GetGrade = "C"
Case 61 To 75
GetGrade = "B"
Case Is > 75
GetGrade = "A"
Case Else
GetGrade = "No Data"
End Select
End Function

 

[Ally]

Sorry Rich but I know it works - the next criteria overtakes the previous one, thus allocating the right grade to the right percentage result.

 

[armin]

Thanks Ally. Your code works on a form or a report. But what about Data Access page?
Also, when this field performs calculation, is there a way to place the result in a table?

Thanks.

 

[Ally]

I'm sorry - not used DAP before. Had a look at one, but I think it will be best to see if someone else can answer that one.

Just had another look - there's an icon for "Field List" and you can add a query there onto the DAP - or are you not able to incorporate your results into the underlying query of your DAP?

 

[armin]

Actually, I'm not using any queries in my database. I'm trying to use only tables, and as far as I know it is not possible to perform any calculations within a table, unlike the query.

 

[Ally]

I think you need to use queries. You can't get very far without them. I did read somewhere that it's better to base all your forms / reports etc on queries - how true that is, I'm not sure, but I very rarely use tables as the underlying recordset for other objects.

You also said about saving your answers into a table. It's not a great idea to store calculations in Access - apart from the fact that you don't really need to do it, when you can just run a query to get the information. But if you decide you really want to, you would need to do a make table followed by update queries.

 

[Rich]

Sorry Ally, I'm having a bad day! 'though I still prefer select case

 

[The_Doc_Man]

Some folks don't like "IF ladders" so here is another way to do the function using SELECT CASE. It is unequivocal. Toss this in a general module somewhere. (Cannot do the PUBLIC part in a class module.) The question-mark answer occurs ONLY when the input was a NULL rather than a valid number. The asterisk occurs ONLY when the input value was neither NULL nor in the range 0 to 100.

Public Function stfLetterGrade( loPct as Long ) as String

Dim stTmp as String

If IsNull( loPct ) Then
    stTmp = "?"
Else
    Select Case loPct
        Case 0 To 59
            stTmp = "F"
        Case 60 To 69
            stTmp = "D"
        Case 70 To 79
            stTmp = "C"
        Case 80 To 89
            stTmp = "B"
        Case 90 To 100
            stTmp = "A"
        Case Else
            stTmp = "*"
    End Select
End If

stLetterGrade = stTmp

End Function

Adjust the ranges so thay are contiguous as shown above. I am using the 60-70-80-90 breakpoints, but make them like you like them. Add other breakpoints if you really wanted an "E" grade or other possible variants.

Now, a comment on storing letter grades...


If your scale is invariant, you NEVER need to store grades because you can always recompute them. That is why you build a function.

I will point out a special case. If your grading scale varies per subject or over time, you should store grades as a combination of student ID, test ID, date, numeric result, AND the letter grade.

If the grading scale is invariant per subject and over time then you only need student ID, test ID, date, and numeric result. A query can recompute that grade for you on the fly.

Learn how to use queries. You can use a query almost anywhere that you can use a table. (Like as a recordsource for a form or a report, or even for other queries.) But unlike tables, queries can have computed results.

 

[armin]

I guess I'll have to use queries instead.

You guys are great. Thanks for your help.