MS Access IIf Function

Question

I have a calculated field on a table at MS Access and I'd like to use the following syntax, but it returns a syntax error pop-up and and I'm wondering which part is wrong. Could you please help with this? Thanks a lot.

IIf([Letter_Grade]="A+",4,IIf([Letter_Grade]="A",4,IIf([Letter_Grade]="A",3.7,IIf([Letter_Grade]="B+",3.3,IIf([Letter_Grade]="B",3,IIf([Letter_Grade]="B",2.7,IIf([Letter_Grade]="C+",2.3,IIf([Letter_Grade]="C",2,IIf([Letter_Grade]="C",1.7,IIf([Letter_Grade]="D+",1.3,IIf([Letter_Grade]="D",1,IIf([Letter_Grade]="D-",0.7,IIf([Letter_Grade]="F",0)))))))))))))

This is a nightmare... I suggest using a lookup table that translates grades to values. — Andre
– Andre, Commented Jan 25, 2022 at 16:17
What Andre said. A small VBA method with a large Select Case statement will be much more readable. — Heinzi
– Heinzi, Commented Jan 27, 2022 at 11:09
Thank you both for the suggestions. I haven't used both (lookup table or VBA method) yet, but I'll definitely take a look! — jhahn
– jhahn, Commented Jan 27, 2022 at 14:44

rob · Accepted Answer · 2022-01-27 10:37:47Z

1

Try adding ",null" after your last expression:

IIf([Letter_Grade]="F",0,null)))))))))))))

Though it may be better to use a query?

answered Jan 27, 2022 at 10:37

rob

364 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jhahn Over a year ago

Thank you very much for the correction. I tired it and it worked! I really appreciate your help!

Sola Oshinowo · Accepted Answer · 2022-01-25 18:42:59Z

0

The function works well, did you put = operator before it.

Attached are some images of the results I got and the function

answered Jan 25, 2022 at 18:42

Sola Oshinowo

6297 silver badges17 bronze badges

3 Comments

jhahn Over a year ago

Thank you so much for your comment and pictures. I tried with = operator before the expression but still returns the same error. What I did was, 1. Open Expression Builder 2. Copy & Paste the expression 3. Click OK. Then, I see the expression in General tab expression properties. However, when I save the table, it returns, "Syntax Error in Expression" (click OK) and then "Errors were encountered during the save operation. Fields were not added. Properties were not updated". I'm a newbie to MS Access. Could you find some problems that I did? Thank you so much.

Sola Oshinowo Over a year ago

Are you adding it to a form or a table? It should be added to a form.

jhahn Over a year ago

Thank you for your comment. I was working on a table.

Collectives™ on Stack Overflow

MS Access IIf Function

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related