Multiple What IIf Function In Query

[Forsi]

Hello
I am trying to use the What IF Function in a Query to determine the Annual
Leave Due to employees based on their job levels and years employed. The
first part of the query works with the job level "M". However, the other
parts do not work. Any assistance given with this nested query would be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years Employed]>3,15,12).

Forsi

 

[Douglas J. Steele]

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so I
can't suggest how to reformat your statement.

 

[Forsi]

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so I
can't suggest how to reformat your statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello
I am trying to use the What IF Function in a Query to determine the Annual
Leave Due to employees based on their job levels and years employed. The
first part of the query works with the job level "M". However, the other
parts do not work. Any assistance given with this nested query would be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years Employed]>3,15,12).

Forsi

 

[Forsi]

Dear Douglas
Thank you for responding. Perhaps if I were to give you an idea of what I
would like to accomplish, it might help you to assist me. I am working on a
Leave database to determine each employee's vacation entitlement yearly. The
conditions are that each employee is assigned a code based on their job level
so Managers - M, Non Management Staff - NM and Non Management staff who
receive the same amount of vacation days as management - NMSA. The policy is
that if an employee with the job level M or NMSA is employed for 3 years or
less he is entitled to 15 days. Once he is working for more than 3 years, he
gets 20 days. If I want to calculate the current amount due (in 2008) I can
use the what if function for example =IIf([Job Level]="M",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf([Job Level]="NMSA",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf(((Now()-[Hire Date])/365)>3,15,12))). However, if I
want to calculate what the leave entitlement of an employee (any job level)
was in a previous year, for example in 2000 (hire date 21 Feb 1997) how would
I now construct this WHAT IF Function to accomplish that?
Thanks
Forsi

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so I
can't suggest how to reformat your statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello
I am trying to use the What IF Function in a Query to determine the Annual
Leave Due to employees based on their job levels and years employed. The
first part of the query works with the job level "M". However, the other
parts do not work. Any assistance given with this nested query would be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years Employed]>3,15,12).

Forsi

 

[Douglas J. Steele]

To calculate how long an employee's been there, you'd use the same
calculation you'd use to determine age, as shown in
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

Assuming you've got a DateOfHire field in your table, you could use
something like:

IIf([Job Level] = "M" OR [Job Level] = "NMSA", IIf(DateDiff("yyyy",
[DateOfHire], Date) - IIf(Format(Date, "mmdd") < Format([DateOfHire],
"mmdd") <=3, 15, 20), IIf(DateDiff("yyyy", [DateOfHire], Date) -
IIf(Format(Date, "mmdd") < Format([DateOfHire], "mmdd") <=3, 12, 15))

Above, I've assumed that there are only the three job levels, meaning is you
know they're not M or NMSA, there's no need to check whether they're NM.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Douglas
Thank you for responding. Perhaps if I were to give you an idea of what I
would like to accomplish, it might help you to assist me. I am working on
a
Leave database to determine each employee's vacation entitlement yearly.
The
conditions are that each employee is assigned a code based on their job
level
so Managers - M, Non Management Staff - NM and Non Management staff who
receive the same amount of vacation days as management - NMSA. The policy
is
that if an employee with the job level M or NMSA is employed for 3 years
or
less he is entitled to 15 days. Once he is working for more than 3 years,
he
gets 20 days. If I want to calculate the current amount due (in 2008) I
can
use the what if function for example =IIf([Job
Level]="M",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf([Job Level]="NMSA",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf(((Now()-[Hire Date])/365)>3,15,12))). However,
if I
want to calculate what the leave entitlement of an employee (any job
level)
was in a previous year, for example in 2000 (hire date 21 Feb 1997) how
would
I now construct this WHAT IF Function to accomplish that?
Thanks
Forsi

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so I
can't suggest how to reformat your statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello
I am trying to use the What IF Function in a Query to determine the
Annual
Leave Due to employees based on their job levels and years employed.
The
first part of the query works with the job level "M". However, the
other
parts do not work. Any assistance given with this nested query would be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years Employed]>3,15,12).

Forsi

 

[Forsi]

Hi Douglas
Thank you for your help. The formula you provided did not work for me.
However, I still found it useful. I fiddled until I figured it out and this
is what I came up with:

Leave Entitlement: IIf([Job Level]="M" Or [Job Level]="NMSA",IIf([Years
Employed]<=3,15,20),IIf([Job Level]="NM",IIf([Years Employed]<=3,12,15)))

Using the greater than formula also works: Leave Entitlement: IIf([Job
Level]="M" Or [Job Level]="NMSA",IIf([Years Employed]>3,20,15),IIf([Job
Level]="NM",IIf([Years Employed]>3,15,12))).

Thanks again and I hope that others find it helpful.
Forsi

To calculate how long an employee's been there, you'd use the same
calculation you'd use to determine age, as shown in
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

Assuming you've got a DateOfHire field in your table, you could use
something like:

IIf([Job Level] = "M" OR [Job Level] = "NMSA", IIf(DateDiff("yyyy",
[DateOfHire], Date) - IIf(Format(Date, "mmdd") < Format([DateOfHire],
"mmdd") <=3, 15, 20), IIf(DateDiff("yyyy", [DateOfHire], Date) -
IIf(Format(Date, "mmdd") < Format([DateOfHire], "mmdd") <=3, 12, 15))

Above, I've assumed that there are only the three job levels, meaning is you
know they're not M or NMSA, there's no need to check whether they're NM.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Douglas
Thank you for responding. Perhaps if I were to give you an idea of what I
would like to accomplish, it might help you to assist me. I am working on
a
Leave database to determine each employee's vacation entitlement yearly.
The
conditions are that each employee is assigned a code based on their job
level
so Managers - M, Non Management Staff - NM and Non Management staff who
receive the same amount of vacation days as management - NMSA. The policy
is
that if an employee with the job level M or NMSA is employed for 3 years
or
less he is entitled to 15 days. Once he is working for more than 3 years,
he
gets 20 days. If I want to calculate the current amount due (in 2008) I
can
use the what if function for example =IIf([Job
Level]="M",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf([Job Level]="NMSA",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf(((Now()-[Hire Date])/365)>3,15,12))). However,
if I
want to calculate what the leave entitlement of an employee (any job
level)
was in a previous year, for example in 2000 (hire date 21 Feb 1997) how
would
I now construct this WHAT IF Function to accomplish that?
Thanks
Forsi

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so I
can't suggest how to reformat your statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello
I am trying to use the What IF Function in a Query to determine the
Annual
Leave Due to employees based on their job levels and years employed.
The
first part of the query works with the job level "M". However, the
other
parts do not work. Any assistance given with this nested query would be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years Employed]>3,15,12).

Forsi

 

[Douglas J. Steele]

Sorry, my fault. I got interrupted typing the first equation, didn't notice
that I hadn't completed it, and then copied the incomplete equation!

IIf([Job Level] = "M" OR [Job Level] = "NMSA", IIf(DateDiff("yyyy",
[DateOfHire], Date) - IIf(Format(Date, "mmdd") < Format([DateOfHire],
"mmdd"), 1, 0) <=3, 15, 20), IIf(DateDiff("yyyy", [DateOfHire], Date) -
IIf(Format(Date, "mmdd") < Format([DateOfHire], "mmdd"), 1, 0) <=3, 12, 15))

Sorry about that!



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas
Thank you for your help. The formula you provided did not work for me.
However, I still found it useful. I fiddled until I figured it out and
this
is what I came up with:

Leave Entitlement: IIf([Job Level]="M" Or [Job Level]="NMSA",IIf([Years
Employed]<=3,15,20),IIf([Job Level]="NM",IIf([Years Employed]<=3,12,15)))

Using the greater than formula also works: Leave Entitlement: IIf([Job
Level]="M" Or [Job Level]="NMSA",IIf([Years Employed]>3,20,15),IIf([Job
Level]="NM",IIf([Years Employed]>3,15,12))).

Thanks again and I hope that others find it helpful.
Forsi

To calculate how long an employee's been there, you'd use the same
calculation you'd use to determine age, as shown in
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

Assuming you've got a DateOfHire field in your table, you could use
something like:

IIf([Job Level] = "M" OR [Job Level] = "NMSA", IIf(DateDiff("yyyy",
[DateOfHire], Date) - IIf(Format(Date, "mmdd") < Format([DateOfHire],
"mmdd") <=3, 15, 20), IIf(DateDiff("yyyy", [DateOfHire], Date) -
IIf(Format(Date, "mmdd") < Format([DateOfHire], "mmdd") <=3, 12, 15))

Above, I've assumed that there are only the three job levels, meaning is
you
know they're not M or NMSA, there's no need to check whether they're NM.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Douglas
Thank you for responding. Perhaps if I were to give you an idea of what
I
would like to accomplish, it might help you to assist me. I am working
on
a
Leave database to determine each employee's vacation entitlement
yearly.
The
conditions are that each employee is assigned a code based on their job
level
so Managers - M, Non Management Staff - NM and Non Management staff who
receive the same amount of vacation days as management - NMSA. The
policy
is
that if an employee with the job level M or NMSA is employed for 3
years
or
less he is entitled to 15 days. Once he is working for more than 3
years,
he
gets 20 days. If I want to calculate the current amount due (in 2008)
I
can
use the what if function for example =IIf([Job
Level]="M",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf([Job Level]="NMSA",IIf(((Now()-[Hire
Date])/365)>3,20,15),IIf(((Now()-[Hire Date])/365)>3,15,12))).
However,
if I
want to calculate what the leave entitlement of an employee (any job
level)
was in a previous year, for example in 2000 (hire date 21 Feb 1997) how
would
I now construct this WHAT IF Function to accomplish that?
Thanks
Forsi



:

Your nesting is incorrect. It needs to be:

IIf(Cond1, Value1, IIf(Cond2, Value2, IIf(Cond3, Value3, Value4)))

Unfortunately, I'm not sure exactly what you for each of the cases, so
I
can't suggest how to reformat your statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello
I am trying to use the What IF Function in a Query to determine the
Annual
Leave Due to employees based on their job levels and years employed.
The
first part of the query works with the job level "M". However, the
other
parts do not work. Any assistance given with this nested query would
be
appreciated.

Leave Entitlement: IIf([Job Level]="M" And [Years
Employed]>3,20,15),IIf([Job Level]="NMSA" And [Years
Employed]>3,20,15),IIf([Job Level]="NM" And [Years
Employed]>3,15,12).

Forsi