Query Expression IIf Statement (1 Viewer)

[BHWATT]

Hey Guys,

I have an access database that contains a form with drop down selections. I am trying to create a field that is populated based on the drop downs of these fields. Each field can be Pending, Approved or Rejected.

I need to have a field called stage that will display Initial if all the fields are populated with Pending. If any of them are Approved and the others are pending then stage would be FINAL.

I have tried
Stage: IIf([Status_Mfg_Engineer] And ([Status_Initiator]) and ([Status_Quality]) and ([Status_Production]) and ([Status_Product_Engineer])="Pending","Initial",IIf([Status_Mfg_Engineer] or ([Status_Initiator]) or ([Status_Quality]) or ([Status_Production]) or ([Status_Product_Engineer])="Approved","Final")

My thought process is it would look at them and see if they were all Pending and if not then it would look at them and see if one of them is Approved but I am not getting the results I was hoping for.

I feel like this is a simple solution that I am just way over thinking.

 

[Gasman]

You have to check each field for pending, not just the last one.
If it is that complicated, I'd probably create a dedicated function.

 

[plog]

Each field can be Pending, Approved or Rejected.

I need to have a field called stage that will display Initial if all the fields are populated with Pending. If any of them are Approved and the others are pending then stage would be FINAL.

1st and least important--your logic isn't complete. You haven't addressed how to handle every case possible. How does Rejected fit into this?

2nd and most important--your tables aren't structured properly. All of the data in these [Status...] fields need to go into a new table with a slightly different structure. Every part of the field name after "Status..." shouldn't be in the names of fields but values in the table.

Lets call your current table tblProjects and let's assume it has an autonumber primary key called idProject. Then this new table with all this status data would be structured like so:

tblStatus
idStatus, autonumber primary key
idProject, number, foreign key back to tbProjects
StatusType, text, this field will hold what type of status (e.g. Initiator, Mfg_Engineer, Production, etc.
StatusValue, text, this field will hold Rejected, Approved, Pending

That one table will now hold every status for all projects. Instead of a field for each status you will add a record to tblStatus for each status.

 

[Pat Hartman]

What Gasman means is your code is invalid. It needs to be like this:

IIf([Status_Mfg_Engineer] = "Pending" And [Status_Initiator] = "Pending" And ....

Also, you need a final argument that specifies the value if both of the IIf()'s are false.

PS, get rid of the extraneous parentheses. They are just clutter. And, as long as your names are properly formed (no embedded spaces, no special characters), you also don't need the []

 

[mike60smart]

Hey Guys,

I have an access database that contains a form with drop down selections. I am trying to create a field that is populated based on the drop downs of these fields. Each field can be Pending, Approved or Rejected.

I need to have a field called stage that will display Initial if all the fields are populated with Pending. If any of them are Approved and the others are pending then stage would be FINAL.

I have tried
Stage: IIf([Status_Mfg_Engineer] And ([Status_Initiator]) and ([Status_Quality]) and ([Status_Production]) and ([Status_Product_Engineer])="Pending","Initial",IIf([Status_Mfg_Engineer] or ([Status_Initiator]) or ([Status_Quality]) or ([Status_Production]) or ([Status_Product_Engineer])="Approved","Final")

My thought process is it would look at them and see if they were all Pending and if not then it would look at them and see if one of them is Approved but I am not getting the results I was hoping for.

I feel like this is a simple solution that I am just way over thinking.

Are you able to upload your database?

 

[LarryE]

Hey Guys,

I have an access database that contains a form with drop down selections. I am trying to create a field that is populated based on the drop downs of these fields. Each field can be Pending, Approved or Rejected.

I need to have a field called stage that will display Initial if all the fields are populated with Pending. If any of them are Approved and the others are pending then stage would be FINAL.

I have tried
Stage: IIf([Status_Mfg_Engineer] And ([Status_Initiator]) and ([Status_Quality]) and ([Status_Production]) and ([Status_Product_Engineer])="Pending","Initial",IIf([Status_Mfg_Engineer] or ([Status_Initiator]) or ([Status_Quality]) or ([Status_Production]) or ([Status_Product_Engineer])="Approved","Final")

My thought process is it would look at them and see if they were all Pending and if not then it would look at them and see if one of them is Approved but I am not getting the results I was hoping for.

I feel like this is a simple solution that I am just way over thinking.

Maybe Select Case would be easier to implement instead of a long complicated IIF statement.
Select Case statement (VBA) | Microsoft Learn

 

[jdraw]

I agree with LarryE, but would ask that you review your criteria separately first. The idea/objective is to confirm the logic that satisfies your requirement before implementing it with Access. Select Case may be a simpler coding construct than a series of IIFs.

 

[ebs17]

Each field can be Pending, Approved or Rejected.

As the RowSource I would use a table, something like this

  1 Pending
 10 Approved
100 Rejected

Stage: IIf(Status_Mfg_Engineer+Status_Initiator+Status_Quality+Status_Production+Status_Product_Engineer<6, "Initial", "Final")

 

[Pat Hartman]

I did forget to mention earlier, that instead of having a "repeating group" of columns that serve the same purpose - set a status, you might want to consider normalizing and instead of 5 columns, you have 5 rows in a child table. In that case, you could do a dcount() to count the number of rows in the child table for this parent record which had a value of Approved. If the count is > 0 then the Stage can be set to Final.

Also, part of this silly normalization process. Having a field that represents the value of other fields also violates normal forms. This can be solved by creating a function that does the count based on a passed in ID for the parent record and the function can return the Stage. This lets you normalize the schema even further by not saving Stage. It is calculated on the fly so it is always accurate. You call the function from your RecordSource query so it is always updated automatically.