Nested IIF Statements

[dwshearer]

I have built a database that tracks employees and the number of days
employed. I created a query to let me search the database for the number of
employees hired in a certain date range. I then created a field, in the
query that would calcute the days employed. I now want to create a field
that will populate with a text that says "Under 30 days"; "Over 30 days";
"Over 60 days"; and "Over 90 days".

So, here is the IIf Statement that I created:

Status: IIF([DaysEmployed]>=90,"Over 90 Days",IIF([DaysEmployed]>=60 and
<90,"Over 60 days",IIF([DaysEmployed]>=30 and <60,"Over 30 Days","Under 30
Days")))

When I go to run the query, I get a syntax error. Could some one please let
me know what I am doing wrong?

Thank you.

 

[Douglas J. Steele]

You can't use

[DaysEmployed]>=60 and <90

You need to use

[DaysEmployed]>=60 and [DaysEmployed]<90

However, since you know that the 2nd IIf statement won't be evaluated unless
DaysEmployed isn't >= 90, you can leave that part out.

Try:

tatus: IIF([DaysEmployed]>=90,"Over 90 Days",IIF([DaysEmployed]>=60 ,"Over
60 days",IIF([DaysEmployed]>=30,"Over 30 Days","Under 30
Days")))

 

[dwshearer]

Thank you Douglas, that works great! I never thought about writing it that
way. It seem so much simplier.

You can't use

[DaysEmployed]>=60 and <90

You need to use

[DaysEmployed]>=60 and [DaysEmployed]<90

However, since you know that the 2nd IIf statement won't be evaluated unless
DaysEmployed isn't >= 90, you can leave that part out.

Try:

tatus: IIF([DaysEmployed]>=90,"Over 90 Days",IIF([DaysEmployed]>=60 ,"Over
60 days",IIF([DaysEmployed]>=30,"Over 30 Days","Under 30
Days")))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have built a database that tracks employees and the number of days
employed. I created a query to let me search the database for the number
of
employees hired in a certain date range. I then created a field, in the
query that would calcute the days employed. I now want to create a field
that will populate with a text that says "Under 30 days"; "Over 30 days";
"Over 60 days"; and "Over 90 days".

So, here is the IIf Statement that I created:

Status: IIF([DaysEmployed]>=90,"Over 90 Days",IIF([DaysEmployed]>=60 and
<90,"Over 60 days",IIF([DaysEmployed]>=30 and <60,"Over 30 Days","Under 30
Days")))

When I go to run the query, I get a syntax error. Could some one please
let
me know what I am doing wrong?

Thank you.