Query Expression IIf Statement

[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.

 

[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")