Nested IIF Statements

G

Guest

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.
 
D

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")))
 
R

raskew via AccessMonster.com

Hi -

The Choose() function adapts well in a situation where the partitions (30 day
periods in your case) are consistent. Here's an example you can copy/paste
to the debug (immediate) window. X represents [DaysEmployment]. Test by
modifying X then stepping thru the 2 lines of code.

x = 29
? choose(int(x/30) + 1, "Under 30 ", "Over 30 ", "Over 60 ", "Over 90 ") &
"Days"
Under 30 Days

HTH - Bob
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")))
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
[quoted text clipped - 15 lines]
Thank you.
 
G

Guest

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

Douglas J. Steele said:
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)


dwshearer said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top