Results 1 to 7 of 7
  1. #1
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24

    Switch function in query design view

    Decision making


    I am using Access 2013.
    Can someone help out there!
    I have already set up a table with the following field name:

    TotalMark
    Result
    Comment

    Now in the query design view how do l set up queries that will perform the following:

    When the TotalMark is between 0 and 39, Result should read G, Comment should read fail
    When the TotalMark is between 40 and 49, Result should read F, Comment should read More Effort
    When the TotalMark is between 50 and 59, Result should read E, Comment should read Can do better
    When the TotalMark is between 60 and 69, Result should read D, Comment should read Good
    When the TotalMark is between 70 and 79, Result should read C, Comment should read well done
    When the TotalMark is between 80 and 89, Result should read B, Comment should read Brilliant
    When the TotalMark is between 90 and 100, Result should read A, Comment should read Excellent

    Thank you

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Rather than create a long calculated field to do it, I think I would rather create a User Defined Function to do it. They are generally easier to maintain that a long, complex function, and if you are using this calculation in more than one place, they are easier to maintain (if you ever needed to change it, you would only need to change it in one place, as opposed to every place that it is used).

  3. #3
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Thank you JoeM

    Iam a novice and l would be glad if you could show me how l can write the switch function for the Result and comment user defined function of the query design view

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am saying I would not use the SWITCH function at all (at least not for the first part).
    I would create a User Defined Function to convert the Total Mark to a letter grade. That UDF would look something like this:
    Code:
    Function ConvertGradeToLetter(totalMark As Variant) As String
    '   This function converts a numeric grade from 0-100 to a letter grade
    
    '   Check to see if totalMark is a number
        If Not IsNumeric(totalMark) Then
            ConvertGradeToLetter = "?"
            Exit Function
        End If
    
    '   Check to make sure totalMark is between 0 and 100
        If (Nz(totalMark, -1) < 0) Or (totalMark > 100) Then
            ConvertGradeToLetter = "?"
            Exit Function
        End If
        
    '   Convert totalMark to letter
        Select Case totalMark
            Case Is >= 90
                ConvertGradeToLetter = "A"
            Case Is >= 80
                ConvertGradeToLetter = "B"
            Case Is >= 70
                ConvertGradeToLetter = "C"
            Case Is >= 60
                ConvertGradeToLetter = "D"
            Case Is >= 50
                ConvertGradeToLetter = "E"
            Case Is >= 40
                ConvertGradeToLetter = "F"
            Case Else
                ConvertGradeToLetter = "G"
        End Select
        
    End Function
    Then, you would just use this like any other function, i.e.
    =ConvertGradeToLetter(90)
    or
    =ConvertGradeToLetter([TotalMarkField])

    Then, for the Comment portion, you have a number of options:
    - Create another UDF for this, following the same structure as I show for the one above
    - Create a reference/lookup table that lists two columns, the Letter Grade and the Comment, and link this to your query on the value returned from the UDF (might need a second query to do that)
    - Use the SWITCH function on the result of the UDF above to return your Comment

  5. #5
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Well, thank you JoeM for showing me a professional approach.

    Like l said from the onset, lam a novice and lam new to access.

    Iam working in the Query design view.

    1. I need an expression for the Result in the Query design view that l can use to evaluate the set conditions.
    2. I need an expression for the comment in the Query design view that l can use to evaluate the set conditions

    I am simply working on a small database. When l reach that level of using your approach, l would gladly use it.

    For now kindly show me the expression using the SWITCH function for the Result field and Comment field.

    Thanking you in advance

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think you may be making the UDF approach into something much harder than it really is (coming up with it may be a little hard, but using one that is already created is pretty easy). All you have to do is to paste the VBA code I gave you in a VBA module. Then you just use it like you would any other function in a query (like "SWITCH").

    So in a calculated field in your query, you would have something like:
    Code:
    LetterGrade: ConvertGradeToLetter([TotalMarkField])
    Personally, I never use the SWITCH function. I usually will use a nested IIF function or a UDF. A nested IIF function would start something like this:
    Code:
    IIF([TotalMarkField]>90,"A",IIF(TotalMarkField]>80,"B",...))
    Here is an article that discusses using a Nested IIF or SWITCH function: http://www.databasechannel.com/Acces...onsSwitch.html

    Here is a little more on the Nested IIF.

    Take a took at these links and give it shot, I bet you can do it! If you run into trouble, post back here.

  7. #7
    sichilaba is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    24
    Evening JoeM

    The nested IIF has worked perfectly.

    Thanks alot. I appreciate and thanks for the patience.

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

Similar Threads

  1. Replies: 5
    Last Post: 12-03-2013, 01:25 PM
  2. Replies: 16
    Last Post: 09-12-2012, 08:39 AM
  3. Replies: 2
    Last Post: 05-23-2012, 01:54 PM
  4. count and switch function in same select query
    By sandlucky in forum Queries
    Replies: 2
    Last Post: 04-08-2011, 11:16 PM
  5. Select Query in Switch Function
    By sandlucky in forum Queries
    Replies: 0
    Last Post: 03-30-2011, 04:54 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