help with nested functions on expression builder?

[Guest]

Hi - I am trying to return a value depending on the stage in which a process
lies (reflected by date).

There are 5 buckets (stages 1-5) within 6 milestones (Mile A-F) that I want
to categorize.

My IIF function is as follows:

STATUS: IIf((([SheetForTable]![Mile A]>=Now()) And ([SheetForTable]![Mile
B]<Now())),"stage 1",IIf((([SheetForTable]![Mile B]>=Now()) And
([SheetForTable]![Mile C]<Now())),"stage 2",IIf((([SheetForTable]![Mile
C]>=Now()) And ([SheetForTable]![Mile D]<Now())),"stage
3",IIf((([SheetForTable]![Mile D]>=Now()) And ([SheetForTable]![Mile
E]<Now())),"stage 4",IIf((([SheetForTable]![Mile E]>=Now()) And
([SheetForTable]![Mile F]<Now())),"stage 5","")))))

This does not return anything at all...and I am thoroughly confused. I was
hoping someone may be able to tell me where the problem lies...

Thanks so much!
M

 

[Tom Ellison]

Dear M:

Here's your code simplified for my viewing:

STATUS:
IIf([Mile A] >= Now() And [Mile B] < Now(), "stage 1",
IIf([Mile B] >= Now() And [Mile C] < Now(), "stage 2",
IIf([Mile C] >= Now() And [Mile D] < Now(), "stage 3",
IIf([Mile D] >= Now() And [Mile E] < Now(), "stage 4",
IIf([Mile E] >= Now() And [Mile F] < Now(), "stage 5",
"")))))

This looks somewhat straight forward. When you say "this does not return
anything at all" I assume it is returning the "" (empty string) condition at
the end. Put something in there temporarily like "not found" just to
confirm this. I suggest you also display (temporarily) in your query the
values Mile A, Mile B, Mile C, Mile D, Mile E, and Now(). This way you can
see what it is comparing in each case and be able to make some judgement as
to why it is not working the way you expect. Seeing what you are doing is
often a beneficial intermediate step.

Now, are Mile A, B, C, D, E, and F all date/time datatype?

Get back to me with what you find this way so we can continue as needed, OK?

Tom Ellison

 

[Guest]

Tom -

Thanks for the suggestion - and you were right, it became clear when I
subbed "real" values. I used your "simplified" code and saw that I had
actually transposed the "<" and ">" for what I was trying to accomplish, and
as a result I naturally returned "not found".

I apologize for taking your time, but appreciate your opening my eyes to
better QA technique.

M

Dear M:

Here's your code simplified for my viewing:

STATUS:
IIf([Mile A] >= Now() And [Mile B] < Now(), "stage 1",
IIf([Mile B] >= Now() And [Mile C] < Now(), "stage 2",
IIf([Mile C] >= Now() And [Mile D] < Now(), "stage 3",
IIf([Mile D] >= Now() And [Mile E] < Now(), "stage 4",
IIf([Mile E] >= Now() And [Mile F] < Now(), "stage 5",
"")))))

This looks somewhat straight forward. When you say "this does not return
anything at all" I assume it is returning the "" (empty string) condition at
the end. Put something in there temporarily like "not found" just to
confirm this. I suggest you also display (temporarily) in your query the
values Mile A, Mile B, Mile C, Mile D, Mile E, and Now(). This way you can
see what it is comparing in each case and be able to make some judgement as
to why it is not working the way you expect. Seeing what you are doing is
often a beneficial intermediate step.

Now, are Mile A, B, C, D, E, and F all date/time datatype?

Get back to me with what you find this way so we can continue as needed, OK?

Tom Ellison

Hi - I am trying to return a value depending on the stage in which a process
lies (reflected by date).

There are 5 buckets (stages 1-5) within 6 milestones (Mile A-F) that I want
to categorize.

My IIF function is as follows:

STATUS: IIf((([SheetForTable]![Mile A]>=Now()) And ([SheetForTable]![Mile
B]<Now())),"stage 1",IIf((([SheetForTable]![Mile B]>=Now()) And
([SheetForTable]![Mile C]<Now())),"stage 2",IIf((([SheetForTable]![Mile
C]>=Now()) And ([SheetForTable]![Mile D]<Now())),"stage
3",IIf((([SheetForTable]![Mile D]>=Now()) And ([SheetForTable]![Mile
E]<Now())),"stage 4",IIf((([SheetForTable]![Mile E]>=Now()) And
([SheetForTable]![Mile F]<Now())),"stage 5","")))))

This does not return anything at all...and I am thoroughly confused. I was
hoping someone may be able to tell me where the problem lies...

Thanks so much!
M

 

[Tom Ellison]

Dear M:

No problem. Opening someone's eyes to a systematic approach has the biggest
dividends in the long run. There's no waste of my time when, rather than
solving some small problem of the moment, I can impart some long term value
to you that could serve you again and again.

Simplifying the code and seeing how "real" value react are important tools
in good programming. Use these whenever possible.

Tom Ellison

Tom -

Thanks for the suggestion - and you were right, it became clear when I
subbed "real" values. I used your "simplified" code and saw that I had
actually transposed the "<" and ">" for what I was trying to accomplish, and
as a result I naturally returned "not found".

I apologize for taking your time, but appreciate your opening my eyes to
better QA technique.

M

Dear M:

Here's your code simplified for my viewing:

STATUS:
IIf([Mile A] >= Now() And [Mile B] < Now(), "stage 1",
IIf([Mile B] >= Now() And [Mile C] < Now(), "stage 2",
IIf([Mile C] >= Now() And [Mile D] < Now(), "stage 3",
IIf([Mile D] >= Now() And [Mile E] < Now(), "stage 4",
IIf([Mile E] >= Now() And [Mile F] < Now(), "stage 5",
"")))))

This looks somewhat straight forward. When you say "this does not return
anything at all" I assume it is returning the "" (empty string) condition at
the end. Put something in there temporarily like "not found" just to
confirm this. I suggest you also display (temporarily) in your query the
values Mile A, Mile B, Mile C, Mile D, Mile E, and Now(). This way you can
see what it is comparing in each case and be able to make some judgement as
to why it is not working the way you expect. Seeing what you are doing is
often a beneficial intermediate step.

Now, are Mile A, B, C, D, E, and F all date/time datatype?

Get back to me with what you find this way so we can continue as needed, OK?

Tom Ellison

Hi - I am trying to return a value depending on the stage in which a process
lies (reflected by date).

There are 5 buckets (stages 1-5) within 6 milestones (Mile A-F) that I want
to categorize.

My IIF function is as follows:

STATUS: IIf((([SheetForTable]![Mile A]>=Now()) And ([SheetForTable]![Mile
B]<Now())),"stage 1",IIf((([SheetForTable]![Mile B]>=Now()) And
([SheetForTable]![Mile C]<Now())),"stage 2",IIf((([SheetForTable]![Mile
C]>=Now()) And ([SheetForTable]![Mile D]<Now())),"stage
3",IIf((([SheetForTable]![Mile D]>=Now()) And ([SheetForTable]![Mile
E]<Now())),"stage 4",IIf((([SheetForTable]![Mile E]>=Now()) And
([SheetForTable]![Mile F]<Now())),"stage 5","")))))

This does not return anything at all...and I am thoroughly confused.

I
was

hoping someone may be able to tell me where the problem lies...

Thanks so much!
M