Selecting Field VBA

[Thompyt]

Field Name.............Field Default Value.............Priority of Fill


AO_Vote.................Null...................... ...........1
O6_Vote.................Null...................... ...........2
GO_Vote.................Null...................... ...........3
Final_Vote...............Null..................... ...........4

Code: AO_Votes: =IIf(Not IsNull([Final_Vote]),[Final_Vote],(IIf(Not IsNull([GO_Vote]),[GO_Vote],(IIf(Not IsNull([O6_Vote]),[O6_Vote],[AO_Vote])))))

Final_Vote must have AO_Vote, and O6_Vote

Level 1:
AO_Vote <>Null for Final_Vote to be filled in

Level 2 and Level 3 Cat 1:
AO_Vote <> Null for O6_Vote to be filled in. AO_Vote and O6_Vote <> Null for Final_Vote to be filled in.

Level 3 Cat 2:
AO_Vote <> Null for O6_Vote to be filled in. AO_Vote and O6_Vote <> Null for GO_Vote to be filled in. AO_Vote and O6_Vote and GO_Vote <> Null for Final_Vote to be filled in.

With the above code I want the highest filled field value to be the true statement.

Level 1
AO_Vote = "Deny"
Final Vote = Deny True = "Deny"

Level 2 and level 3 Cat 1
AO_Vote = "Deny" and O6_Vote = "Approve"
Final_Vote = Approve True = "Approve"

Level 3 Cat 2
AO_Vote = "Deny" and O6_Vote = "Approve" and GO_Vote = "Denied"
Final_Vote="Denied" True="Denied"

Its a heiarchal voting system AO is the little guy for the simple stuff. O6 outvotes AO and is for the higher level stuff. GO outvotes O6 for the high priority stuff. Whever the level is, is when Final Vote can be filled in.

I am using the following SQL in a query.

TRANSFORM Count([CR_ID])-1 AS R_ID
SELECT Chng_ReqQry.Level, Count([Level])-5 AS [HB Totals]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Sub_No)=0) AND ((Chng_ReqQry.Date_Closed) Between Date()-7 And Now()))
GROUP BY Chng_ReqQry.Level
PIVOT Chng_ReqQry.AO_Votes;

I cannot get "Withdrawn" amount to show up in the count. the actual field value is:
AO_Vote = "Withdraw"
O6_Vote = "Withdraw"
GO_Vote = Null
Final_Vote = "Withdrawn"

[orange]

I think you are confusing readers (me for sure) with your Level 2 and level 3 Cat 1 etc.

Can you try your explanation in simpler terms, or example?

[Thompyt]

Its convoluted. The level sets the requirements. Lowest needs 1 field filled in to allow Final_vote to be populated. The next level needs 2 fields filled in, with highest level needing 3 fields filled in. It is sequential.

So I am trying to code that backwards. If the Final_Vote field is filled in then it displays that first. If Final_Vote is Null then it checks the GO_Vote field. If GO_Vote is Null then it checks the O6_Vote and so on. Where it is not Null that is what it displays.

The Query:



The Outcome:



If you look at the software level - The total amount is correct - 5. The other fields have a 3 and a 1 total, I am missing the one where it is in Withdrawn. That should be comming from the Final_Vote Field.