Nested if - in an query

[Norma]

I am pasting some code that I wrote to see if anyone can spot the
problem. This code figures Shifts needed to finish a set schedule
amount. Each shift is figured as 7.5 hours. It worked great until I
tried to make this a nested If function. You see, there are 4 fields
that could have a 'standard' in it. Only one has a standard in it and
I need to look at all the fields to see which one has a standard in it
and then use that standard in the calculations.

The [2004Standard] and the [NewStandard] field are calculated
correctly but the [CFInternalEstimate] and [CFSubmittedToPitts] are
not calculating anything.
Am I doing this the wrong way?

IIf(([UPC's]![2004standard]>0), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![2004standard]*7.5),
_
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![NewStandard]*7.5)),
_
iif(([UPC's]![CFInternalEstimate]>0),([FSAPROD_RMRESR]![RREXQY])/ _
([UPC's]![PeopleNeeded]*[UPC's]![CFInternalEstimate]*7.5), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![CFSubmittedToPitts]*7.5))

Any help is greatly appreciated...

Norma

[James Fortune]

nj**********@suscom.net (Norma) wrote in message news:<20**************************@posting.google. com>...

I am pasting some code that I wrote to see if anyone can spot the
problem. This code figures Shifts needed to finish a set schedule
amount. Each shift is figured as 7.5 hours. It worked great until I
tried to make this a nested If function. You see, there are 4 fields
that could have a 'standard' in it. Only one has a standard in it and
I need to look at all the fields to see which one has a standard in it
and then use that standard in the calculations.

The [2004Standard] and the [NewStandard] field are calculated
correctly but the [CFInternalEstimate] and [CFSubmittedToPitts] are
not calculating anything.
Am I doing this the wrong way?

IIf(([UPC's]![2004standard]>0), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![2004standard]*7.5),
_
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![NewStandard]*7.5)),
_
iif(([UPC's]![CFInternalEstimate]>0),([FSAPROD_RMRESR]![RREXQY])/ _
([UPC's]![PeopleNeeded]*[UPC's]![CFInternalEstimate]*7.5), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![CFSubmittedToPitts]*7.5))

Any help is greatly appreciated...

Norma

If the 'standard' is Null or zero for the other three fields then skip
using IIF entirely:

([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*(Nz([UPC's]![2004standard])
+ Nz([UPC's]![NewStandard]) + Nz([UPC's]![CFInternalEstimate]) +
Nz([UPC's]![CFSubmittedToPitts]))*7.5

This is ugly but it's pretty compared to nesting all those IIF's.

James A. Fortune

[Steve Jorgensen]

On 26 Jul 2004 03:00:39 -0700, ja******@oakland.edu (James Fortune) wrote:

nj**********@suscom.net (Norma) wrote in message news:<20**************************@posting.google. com>...

I am pasting some code that I wrote to see if anyone can spot the
problem. This code figures Shifts needed to finish a set schedule
amount. Each shift is figured as 7.5 hours. It worked great until I
tried to make this a nested If function. You see, there are 4 fields
that could have a 'standard' in it. Only one has a standard in it and
I need to look at all the fields to see which one has a standard in it
and then use that standard in the calculations.

The [2004Standard] and the [NewStandard] field are calculated
correctly but the [CFInternalEstimate] and [CFSubmittedToPitts] are
not calculating anything.
Am I doing this the wrong way?

IIf(([UPC's]![2004standard]>0), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![2004standard]*7.5),
_
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![NewStandard]*7.5)),
_
iif(([UPC's]![CFInternalEstimate]>0),([FSAPROD_RMRESR]![RREXQY])/ _
([UPC's]![PeopleNeeded]*[UPC's]![CFInternalEstimate]*7.5), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![CFSubmittedToPitts]*7.5))

Any help is greatly appreciated...

Norma

If the 'standard' is Null or zero for the other three fields then skip
using IIF entirely:

([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*(Nz([UPC's]![2004standard])
+ Nz([UPC's]![NewStandard]) + Nz([UPC's]![CFInternalEstimate]) +
Nz([UPC's]![CFSubmittedToPitts]))*7.5

This is ugly but it's pretty compared to nesting all those IIF's.

James A. Fortune

Nz is also slower to execute than IIf, but to quote Knuth, "Premature
optimization is the root of all evil." Make it as simple and clear as
possible (so use nz) at all costs, then optimize it later if necessary.

[Jeff Leckrone]

Iif statements are formatted like this:
iif(<condition>, <do this if true>, <do this if false>)

I think that in order to accomplish what you are attempting you need
more than two iifs. It should look something like this:

IIf(([UPC's]![2004standard]>0), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![2004standard]*7.5),
iif(([UPC's]![NewStandard] > 0),
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![NewStandard]*7.5),
iif(([UPC's]![CFInternalEstimate]>0),([FSAPROD_RMRESR]![RREXQY])/ _
([UPC's]![PeopleNeeded]*[UPC's]![CFInternalEstimate]*7.5), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![CFSubmittedToPitts]*7.5))))

It might look a little neater and be easier to follow if you used
nested IF..THEN..ELSE statements. Just a thought.

The [2004Standard] and the [NewStandard] field are calculated
correctly but the [CFInternalEstimate] and [CFSubmittedToPitts] are
not calculating anything.
IIf(([UPC's]![2004standard]>0), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![2004standard]*7.5),
_
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![NewStandard]*7.5)),
_
iif(([UPC's]![CFInternalEstimate]>0),([FSAPROD_RMRESR]![RREXQY])/ _
([UPC's]![PeopleNeeded]*[UPC's]![CFInternalEstimate]*7.5), _
([FSAPROD_RMRESR]![RREXQY])/([UPC's]![PeopleNeeded]*[UPC's]![CFSubmittedToPitts]*7.5)) Am I doing this the wrong way?
Any help is greatly appreciated...

Norma

[James Fortune]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<a4********************************@4ax.com>. ..

Nz is also slower to execute than IIf, but to quote Knuth, "Premature
optimization is the root of all evil." Make it as simple and clear as
possible (so use nz) at all costs, then optimize it later if necessary.

Steve politely and correctly notes that I should have added, "If
you're sure the other values will be zero you can omit the Nz()
functions."

James A. Fortune

[Norma]

ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<a4********************************@4ax.com>. ..

Nz is also slower to execute than IIf, but to quote Knuth, "Premature
optimization is the root of all evil." Make it as simple and clear as
possible (so use nz) at all costs, then optimize it later if necessary.

Steve politely and correctly notes that I should have added, "If
you're sure the other values will be zero you can omit the Nz()
functions."

James A. Fortune

Thank you so much Steve and James.
I used the Nz code. It works great!

You guys are the best!!