Results 1 to 8 of 8
  1. #1
    sciencegeek is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    3

    VBA If/then calculations for Query

    I want to be able to populate a field in a query that calculates GPA based on a grade and the number of credit hours for that class.
    Having no VBA experience, I can't get past what I've written below. Based on what I've seen in other forums, this might not even be the best way to do this, and I may have just confused myself more.

    Anyone have a way to do this more efficiently, or can anyone tell me what I'm missing?




    Function GradePointsCalculation(grade as string)

    If [grade] = "A+" Then
    GradePointsCalculation = [credit hours] * 4
    If [grade] = "A" Then
    GradePointsCalculation = [credit hours] * 4
    If [grade] = "A-" Then
    GradePointsCalculation = [credit hours] * 3.67
    If [grade] = "B+" Then
    GradePointsCalculation = [credit hours] * 3.33
    If [grade] = "B" Then
    GradePointsCalculation = [credit hours] * 3
    If [grade] = "B-" Then
    GradePointsCalculation = [credit hours] * 2.67
    If [grade] = "C+" Then
    GradePointsCalculation = [credit hours] * 2.33
    If [grade] = "C" Then
    GradePointsCalculation = [credit hours] * 2
    If [grade] = "C-" Then
    GradePointsCalculation = [credit hours] * 1.67
    If [grade] = "D" Then
    GradePointsCalculation = [credit hours] * 1
    If [grade] = "F" Then
    GradePointsCalculation = [credit hours] * 1
    Else
    GradePointsCalculation = ""

    End Function

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    What are you trying to do, call a function from a query? You need your function to return something.
    Function GradePointsCalculation(strGrade as string) as string

    And instead of using If Else If Else ... Try a Select Case ... Something like
    Select Case strGrade

    Case "A+"
    GradePointsCalculation = "[credit hours] * 4"
    Case "A"
    GradePointsCalculation = "[credit hours] * 4"

    End Select

  3. #3
    sciencegeek is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    3
    If the student got an A in a 4 credit class, I want this extra field to read "12", based on these calculations.

    I tried using Case and got the "[credit hours] * 4" in the field. Should I change "strGrade as string" to "strGrade as Single" or remove the quotes? I keep getting a pop-up when I run the query that asks for a value. I want a value calculated for each row!

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Can you post your SQL from your query here?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with ItsMe - it would be better to use "Select Case".
    Something like this:
    Code:
    Function GradePointsCalculation(strGrade As String)
    
        Select Case strGrade
            Case "A+", "A"
                GradePointsCalculation = [credit hours] * 4
            Case "A-"
                GradePointsCalculation = [credit hours] * 3.67
            Case "B+"
                GradePointsCalculation = [credit hours] * 3.33
            Case "B"
                GradePointsCalculation = [credit hours] * 3
            Case "B-"
                GradePointsCalculation = [credit hours] * 2.67
            Case "C+"
                GradePointsCalculation = [credit hours] * 2.33
            Case "C"
                GradePointsCalculation = [credit hours] * 2
            Case "C-"
                GradePointsCalculation = [credit hours] * 1.67
            Case "D", "F"
                GradePointsCalculation = [credit hours] * 1
            Case Else
                GradePointsCalculation = ""
        End Select
    
    End Function
    I would also consider passing the credit hours as an argument:
    Code:
    Function GradePointsCalculation(strGrade As String, dblHrs As Double)
    then change "[credit hours]" to "dblHrs".

    BTW, you shouldn't use spaces in object names.

  6. #6
    sciencegeek is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    3
    I think what I am really looking for is a calculated field in my original table, and not in a query. Thanks for helping me think through and look into this issue.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, if that is what you want to try to do.
    I NEVER use calculated fields in tables - IMHO, calculations should be in queries or UDFs.

  8. #8
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    =Switch([grade]="A+",4,[grade]="A",4,[grade]="A-",3.67, [... follow pattern here ...], true, "")

    The Switch function runs until a test hits true, then stops. The last true statement ensures an "else" consideration.


    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  2. Calculations in a query
    By dichotomous2013 in forum Access
    Replies: 4
    Last Post: 02-19-2013, 06:55 AM
  3. Calculations in a query
    By bishop0071 in forum Queries
    Replies: 1
    Last Post: 01-27-2013, 03:54 PM
  4. Conditional calculations in a query?
    By Datech in forum Queries
    Replies: 3
    Last Post: 06-17-2012, 09:39 PM
  5. Calculations in Query
    By jdhaldane in forum Queries
    Replies: 5
    Last Post: 12-10-2010, 05:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums