IIf Statement

[dharsh]

In my table I have the following 4 fields with the associated date field:
Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015



I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?

=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))

[RuralGuy]

Why not use a Select Case structure in a standard module for this? It would be much easier to read and maintain.

[dharsh]

I have never done that. Everything I have done in Access up to this point is in queries and simple formulas.

[RuralGuy]

Nested IIF's are hard for me to follow, sorry. Start with a single IIF and see what the results are. That build on it when you resolve the issue.

[Zephyx]

I could be wrong, but it appears that you just need ) after the first [Bronze]
so basically at the very front of your code, =IIf(Date()<[Bronze])....
^

[ssanfu]

I created a table, entered the dates you provided, created a query and entered the IIF() formula, all from your post#1. When I executed the query, the result was "Standard". I made no changes to what you provided.

I changed the date for "Bronze" to 11/1/2012 and the result was "Bronze".
I changed the date for "Bronze" back to 11/1/2013 and the result was "Standard".

So as far as the formula is concerned - No problems. Works for me.........