1. Registered User
Join Date
Feb 2008
Posts
27

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. SQL Consultant
Join Date
Apr 2002
Location
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

3. King of Understatement
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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!

5. Registered User
Join Date
Feb 2008
Posts
27
Thank you all for the help! It worked, I wasn't putting the parenthensis in the right places. I really appreciate it!

6. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Rank: [Active Grade] & [total # Loans handled]

note that & performs concatenation

8. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

10. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location