CODEWITHSUNDEEP
vbafunctionms-access
Marnett05
Marnett05

Reputation: 1

MS Access Query Function for adding a column based on existing data

I have relied on other's posts here in the past, and I was hoping someone might be able to give me a bit of help. This is my first time making my own VBA function.

I am trying to create a query that looks at one column, in this case a calculated percentage field, and places a value in the new column based on the range the percentage falls in. Think of it like a grade in school: 80%-90% would be a B.

The issue comes in with the criteria. There is also a third column that I am including in this function that changes the percentage ranges, like if Science class had different ranges for what was an A, B, C, and so forth. I have a total of 8 classes and 5 grades that will need returned, so IIF() statements in the query seemed a bit much. Relevant information is included below:

  1. Query name: Test_Percentage_Qry

  2. 'Class' column name: Source

  3. Percent column name: CalcPerc

  4. 'Grade' column name: Score

Existing function code:

Public Function fFCRScore() As String
If Test_Percentage_Qry.Source = "Science" Then
    If Test_Percentage_Qry.CalcPerc = "100.00%" Then
        fFCRScore = 10
    ElseIf Test_Percentage_Qry.CalcPerc >= "85.00%" Then
        fFCRScore = 10
    ElseIf Test_Percentage_Qry.CalcPerc >= "80.00%" And Test_Percentage_Qry.CalcPerc < "85.00%" Then
        fFCRScore = 8
    ElseIf Test_Percentage_Qry.CalcPerc >= "75.00%" And Test_Percentage_Qry.CalcPerc < "80.00%" Then
        fFCRScore = 6
    ElseIf Test_Percentage_Qry.CalcPerc >= "70.00%" And Test_Percentage_Qry.CalcPerc < "75.00%" Then
        fFCRScore = 4
    ElseIf Test_Percentage_Qry.CalcPerc < "70.00%" Then
        fFCRScore = 2
    End If
End If

Thanks for the help in advance!

Edit: I should point out I'm getting a

424 Object Required Runtime Error

I'm thinking it's how I'm referring to the column in my query, but not 100% sure. I never seem to remember the way that MS Access wants that done.

Upvotes: 0

Views: 99

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Use a table with the grade thresholds for each subject and join them. To demonstrate see below:

Create two tables (tblResults, tblSubjects)

enter image description here

Create a query (inner join of two tables on Subject)

enter image description here

which yields the following output for you to use IIF() statements:

enter image description here

Using this example something like this

IIf([Result] > [AStart], "A",
    IIf([result] > [Bstart], "B",
        IIf([result] > [Cstart], "C", "Ungraded"))) As SubjectScore

enter image description here

Please note image is without the alias SubjectScore

Additionally, this SELECT query can then be modified to an insert/update query for other needs.

Upvotes: 1

Related Questions