Any help appeciated!

[ND1994]

I am just learning to build a simple access databse for an admission office that I run. I am learning access and simple vb just by doing. One snag I have run into while trying to get this thing up and running is with compound if statements (that's what I have been calling them anyway). I would like field [Math Level] to return "Standard" if field [Math Score] is between "29" and "40" and to return Honors if between "30" and "40". So, my first attempt was the following expression: IIF([Math Score] between "29" and "40", "Standard", "Honors"). This looked good until I realized that every record with a blank [Math Score] returned "Honors" as well. I guessed that I needed some sort of IsNull function within the statement, so I made a feeble attempt to add a second If statement as the otherwise command: IIF([Math Score] between "29" and "40", "Standard", IIf(IsNull[Math Score], "", "Honors")). That obviously didn't work. Go easy on me. I realize it probably looks ridiculous. Any advice is welcomed!

 

[Jon K]

Change the [Math Score] field from a text field to a numeric field, and remove the quotes surrounding the numbers in the expression.

IsNull() is a function and needs the brackets.

I think you may also need to include those who are "Below Standard".


IIf([Math Score] < 29, "Below Standard", IIf([Math Score] Between 29 And 40, "Standard", IIf(IsNull([Math Score]), Null, "Honors")))

Alternatively, you can use:-
IIf([Math Score] < 29, "Below Standard", IIf([Math Score] Between 29 And 40, "Standard", IIf([Math Score] > 40, "Honors", Null)))


Note: In a query, we normally use Null instead of "". If you need to return a zero length string e.g. in VBA, you can change Null to "".
.

 

[pono1]

Can you go over the logic again?

(Score>=29 AND Score <=40) = "Standard"
Everything else = "Honors"

No?

Edit: Never mind -- Jon K is here and seems to understand.

Regards,
Tim

 

[ND1994]

Thanks!

Your suggestion helped me fix it. Thanks for the guidance. I ended up writing this expression which works and it now seems so simple:

IIf([Math Score]<30, "Standard", IIf([Math Score] Between 30 And 40,"Honors",Null))