Another control affecting the output of a control

[Thompyt]

In a report Final_Vote control I am trying to state If the control O6_Vote = Hold, then no matter what is in the AO_Vote it equals "Hold"

=IIf([O6_Vote]="Hold",[AO_Vote]="Hold",[AO_Vote]) And IIf([AO_Vote]="Defer","Deferred",IIf([AO_Vote]="Approve","Approved",IIf([AO_Vote]="Deny","Denied",IIf([AO_Vote]="Withdraw","Withdrawn",[AO_Vote]))))



In this code I end up with a 0 in the affected field with others = -1

[June7]

Try:

=Switch([O6_Vote]="Hold", "Hold", [AO_Vote]="Defer", "Deferred", [AO_Vote]="Approve", "Approved", [AO_Vote]="Deny", "Denied", [AO_Vote]="Withdraw", "Withdrawn")

[Thompyt]

Thanks June Thats a lot cleaner than mine. Works like a charm except for the rows following where O6_Vote =Hold, then control is blank. Do you need an If then where O6_Vote is blank or <> "Hold"?

[June7]

Should not. If O6_Vote is null, the expression is False and the other items will process.

Why are there two fields? Why isn't Hold just another value under AO_Vote?

Post example data.

[Thompyt]

There are 3 levels of voting going on. Sequentially 1. AORB 2. CCB 3. GO level. You have to have an answer on AORB to get to CCB, then answer on CCB to get to GO level. But the levels are predetermined earlier by the severity of risk. If AORB says approved, then CCB could say they need more info, and can put it on hold. I do not want to change the AORB vote per se to reflect it on the report, and then there will be a timestamp (date) for each level of vote for statistical counts. Number of days at each level. Thus in the report count, I need the current levels vote to be displayed.

[June7]

Example data?

[Thompyt]

AORB.....CMB.....GO.....Final
Approve......................Approve Level 1
Deny......Deny.............Denied.....Level 2
Approve..Hold..............Open.......Level 2

[June7]

Not seeing the field names from the original post. Why does one field use "Denied" instead of "Deny"?

First record has only 3 values.

[Thompyt]

AO_Vote.......O6_Vote….....GO_Vote…......Final_Vot e……Level
Approve........................................... .......Approved…… Level 1
Deny.............Denied………………….............Denied… ........Level 2
Approve……...Hold……………………..............Open........ .....Level 2
Approve……Approve…...Approved…...Approved……Level 3

Level 1 Approval Authority – AO_Vote
Level 2 Approval Authority – O6-Vote
Level 3 Approval Authority – GO_Vote

Level 1: AO_Vote - Approves………..Final_Vote - Past tense outcome
Level 2: AO_Vote - Recommends, O6_Vote - Approves, Final_Vote - Past tense outcome
Level 3: AO_Vote - Recommends, O6_Vote - Recommends,GO_Vote - Approves, Final_Vote – Past tense outcome

[June7]

I calculated the Final_Vote in query and in textbox and both return the results shown in the example for Final_Vote except for the second Level 2 record. Why does it show Open?

[Thompyt]

Open is a case where there has been no final conclusion, the item is still open. I use it as a filter in the queries/reports.


Example of what I get. Notice the 2 Hold in ther Open section. The following are also in Hold status, yet don't display.

[June7]

The rule you gave in OP said if 06_Vote is Hold then return Hold no matter what is in the AO_Vote. That's what my suggested expression does.

There is another rule to consider in the calc?

[Thompyt]

No other rule right now. I was wondering why it did not make CR72-75 Hold as well.
If O6 is hold then it should output Hold in the open section even though in the dB it is another value - approve/deny/pending/hold.

[June7]

Without analyzing actual data, I have no idea.

[Thompyt]

NIE Change Request - Copy.zipNIE Change Request - Copy.zip