Nested If Statment

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I have a query with the following fields

Emp#
Emp Name
Safe Hours Amount

I am trying to build a new field in my query with a "Nested IF" Statement
that will perform the following.

1. If "Safe Hours Amount" is >= 800 AND <=999, then "Safe Hours Amount" * .04
2. If "Safe Hours Amount" is >= 1000 AND <=1199, then "Safe Hours Amount" *
..05
3. If "Safe Hours Amount" is >= 1200 AND <=1399, then "Safe Hours Amount" *
..06
4. If "Safe Hours Amount" is >= 1400 AND <=1599, then "Safe Hours Amount" *
..07
5. If "Safe Hours Amount" is >= 1600, then "Safe Hours Amount" * .08

Can anyone help with creating this statement

Thanks

W
 
Try something along the lines of
SELECT Switch([Safe Hours Amount] < 800, ???, [Safe Hours Amount] < 1000,
[Safe Hours Amount] * 0.04, [Safe Hours Amount] < 1200, [Safe Hours Amount]
* 0.05, [Safe Hours Amount] < 1400, [Safe Hours Amount] * 0.06, [Safe Hours
Amount] < 1600, [Safe Hours Amount] * 0.07, True, [Safe Hours Amount] *
0.08) As AdjustedSafeHours, etc

For the solution to be watertight, you will need to supply a course of
action to cater for [Safe Hours Amount] being below 800.

Hope This Helps
Gerald Stanley MCSD
 
Thanks Gerald.....I tried your solution but I must be missing something....I
am not very familiar with a statement like this....and certainly not up on
this enought to know where to begin to "tweak" the code so it will work.....I
just copied and pasted the code into my query and tried to run it......when
it failed...I started to look for anything that I might need to edit. so I'd
like to ask a few more "probably stupid" questions...

1. I'm not sure what the "Switch" word is for....as well as the condition
for "< 800, ???"
2. I did remove the "As AdjustedSafeHours, etc" from my copy/paste operation

If it helps at all....My "Query" name is simply "Query1" that I am trying to
build this into

Finally, what part of the solution is keeping a given value from being true
on multiple conditions...in other words.....it looks like most of the code is
only using the "<" symbol for the condition....Wouldn't a number like "1000"
meet multiple conditions since it is < 1200 and 1400 and 1600, etc....?

I'm not concerned about any value less than 800.....if the "Safe Hours
Amount" field is less than 800 the value of the cell can be "blank" or "null"

Any other thoughts????
Thank you for your time

W



Gerald Stanley said:
Try something along the lines of
SELECT Switch([Safe Hours Amount] < 800, ???, [Safe Hours Amount] < 1000,
[Safe Hours Amount] * 0.04, [Safe Hours Amount] < 1200, [Safe Hours Amount]
* 0.05, [Safe Hours Amount] < 1400, [Safe Hours Amount] * 0.06, [Safe Hours
Amount] < 1600, [Safe Hours Amount] * 0.07, True, [Safe Hours Amount] *
0.08) As AdjustedSafeHours, etc

For the solution to be watertight, you will need to supply a course of
action to cater for [Safe Hours Amount] being below 800.

Hope This Helps
Gerald Stanley MCSD

WDP said:
Hi:

I have a query with the following fields

Emp#
Emp Name
Safe Hours Amount

I am trying to build a new field in my query with a "Nested IF" Statement
that will perform the following.

1. If "Safe Hours Amount" is >= 800 AND <=999, then "Safe Hours Amount" * .04
2. If "Safe Hours Amount" is >= 1000 AND <=1199, then "Safe Hours Amount" *
.05
3. If "Safe Hours Amount" is >= 1200 AND <=1399, then "Safe Hours Amount" *
.06
4. If "Safe Hours Amount" is >= 1400 AND <=1599, then "Safe Hours Amount" *
.07
5. If "Safe Hours Amount" is >= 1600, then "Safe Hours Amount" * .08

Can anyone help with creating this statement

Thanks

W
Click to expand...
Click to expand...
 
How about looking at it a little differently from a formula perspective
instead of a nested if. This, to me, is a heck of a lot simpler:

Let X = Safe Hours Amount
Let Y = New Field in Query

Y = X * (INT(X/200)/100)

I didn't test it but I think it will work. Simply use the INT function
(which takes the Integer of the (Safe Hours Amount divided by 200). Notice
result will be 4 for any Safe Hours Amount between 800 and 999.99 inclusive.
Then divide result by 100 to get .04.
 
I would model this with a table of ranges and mutlipliers. Knowing how
business processes work in my office, I would expect that as soon as I had a
solution that involved "hard-coded" multipliers based on safe hour amounts,
someone would change the ranges and rates.

If you want to maintain expressions, then do as others have suggested with
Switch() or other solutions. If you want to make your application
maintainable with data, then create a table like

MinHrs MaxHrs Multiplier
800 999 .04
1000 1199 .05
etc

You could also do this with a single hours field for either the Min or Max
values.
 
In answer to your questions

1. Switch is a function similar to IIf for providing actions depending upon
criteria being met. It works using parameter pairs, the first being the
condition parameter
and the second being the action parameter.
So, the first condition is [Safe Hours Amount] < 800 and the action is ???
as you did not specify what should happen in this case. If you do not want
any action, then Null should suffice.

2. The code snippet was only meant to show how the one column (which I
named AdjustedSafeHours) was to appear in the query.

3. When the Switch function is in action, only the action that is paired
with the first condition that evaluates to True takes place; hence the need
to specify what action should take place on values < 800.

I would endorse Duane's solution if there is any realistic liklihood that
these multipliers or the levels to which they apply would be changed as a
business requirement.

Hope This Helps
Gerald Stanley MCSD
WDP said:
Thanks Gerald.....I tried your solution but I must be missing something....I
am not very familiar with a statement like this....and certainly not up on
this enought to know where to begin to "tweak" the code so it will work.....I
just copied and pasted the code into my query and tried to run it......when
it failed...I started to look for anything that I might need to edit. so I'd
like to ask a few more "probably stupid" questions...

1. I'm not sure what the "Switch" word is for....as well as the condition
for "< 800, ???"
2. I did remove the "As AdjustedSafeHours, etc" from my copy/paste operation

If it helps at all....My "Query" name is simply "Query1" that I am trying to
build this into

Finally, what part of the solution is keeping a given value from being true
on multiple conditions...in other words.....it looks like most of the code is
only using the "<" symbol for the condition....Wouldn't a number like "1000"
meet multiple conditions since it is < 1200 and 1400 and 1600, etc....?

I'm not concerned about any value less than 800.....if the "Safe Hours
Amount" field is less than 800 the value of the cell can be "blank" or "null"

Any other thoughts????
Thank you for your time

W



Gerald Stanley said:
Try something along the lines of
SELECT Switch([Safe Hours Amount] < 800, ???, [Safe Hours Amount] < 1000,
[Safe Hours Amount] * 0.04, [Safe Hours Amount] < 1200, [Safe Hours Amount]
* 0.05, [Safe Hours Amount] < 1400, [Safe Hours Amount] * 0.06, [Safe Hours
Amount] < 1600, [Safe Hours Amount] * 0.07, True, [Safe Hours Amount] *
0.08) As AdjustedSafeHours, etc

For the solution to be watertight, you will need to supply a course of
action to cater for [Safe Hours Amount] being below 800.

Hope This Helps
Gerald Stanley MCSD

WDP said:
Hi:

I have a query with the following fields

Emp#
Emp Name
Safe Hours Amount

I am trying to build a new field in my query with a "Nested IF" Statement
that will perform the following.

1. If "Safe Hours Amount" is >= 800 AND <=999, then "Safe Hours Amount" * .04
2. If "Safe Hours Amount" is >= 1000 AND <=1199, then "Safe Hours Amount" *
.05
3. If "Safe Hours Amount" is >= 1200 AND <=1399, then "Safe Hours Amount" *
.06
4. If "Safe Hours Amount" is >= 1400 AND <=1599, then "Safe Hours Amount" *
.07
5. If "Safe Hours Amount" is >= 1600, then "Safe Hours Amount" * .08

Can anyone help with creating this statement

Thanks

W
Click to expand...
Click to expand...
Click to expand...
 
Back
Top