Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: IIF Function Help

    I am trying to insert an Alpha value for percentages, much like school, 100% = A, 99.99% to 90.00 = B and so on. I have been trying to get this function down for a while and cannot seem to get it right. Here is an example of what I was trying to do:

    IIF([record%] = 100%, "A") IIF ([record%] <= 99.99% or [record] >89.99%, "B")

    If i just do the first part I get the 100% to have the A, but when I add the rest it does not work, anyone have any ideas?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IIF([record%] = 100, 'A',
    IIF([record%] >= 90, 'B',
    IIF([record%] >= 70, 'C','D')))


    see if you can figure out how that works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm gonna meekly suggest that you stick this in a table rather than in code.

    Feel free to ask why and\ or how

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    good suggestion Poots; especially because not all grade boundaries are the same in all subjects!

    Heck, I sat an exam once and you had to get 60% or more for an A!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2008
    Posts
    27

    Cool

    Thank you all for the help! It worked, I wasn't putting the parenthensis in the right places. I really appreciate it!

  6. #6
    Join Date
    Feb 2008
    Posts
    27

    Sequence Help

    I am working on a Database to grade Vendors we use. I was able to give them and typical grade of A, B, C, D, with this formula: Active Grade: IIf([Active Summary -Greenpoint]![% On Time]=1,"A",IIf([Active Summary -Greenpoint]![% On Time]>=0.9,"B",IIf([Active Summary -Greenpoint]![% On Time]>=0.5,"C",IIf([Active Summary -Greenpoint]![% On Time]>=0,"D"))))

    What I want to do is rank each one within the grade though. For example, if I have 10 people that have A's I want to to A1, A2, A3 and so on. The Letter is based on a percent and the number should be based upon a [total # Loans handled].

    How would I be able to rank them as such?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Rank: [Active Grade] & [total # Loans handled]

    note that & performs concatenation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Posts
    27
    I did this and just get an error message in the fields, i think that I need to rank the Total loans first, but am unsure how to do that.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will need to show more information -- your table layout, your actual query, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2008
    Posts
    27
    Here are the basic fields:

    Field1: [Attorney Name]
    Field2: [Total Loan Count]
    Field3: [% on Time]
    Fied4: Active Grade: IIf([Active Summary - FTB]![% On Time]=1,"A",IIf([Active Summary - FTB]![% On Time]>=0.9,"B",IIf([Active Summary - FTB]![% On Time]>=0.5,"C",IIf([Active Summary - FTB]![% On Time]>=0,"D"))))

    I now want Field 5 that gives me the Active Grade plus Total loan count for a final rank of A1, A2, B1, B2, C1, C2

    Where
    Alpha = % on time
    Numberic = Total Loans (Descending)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Field5: [Active Grade] & [Total Loan Count]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •