barter27293
Registered User.
- Local time
- Today, 21:28
- Joined
- Dec 5, 2016
- Messages
- 10
Hello,
I'm currently working on a database to track items through a manufacturing plant, so have multiple checkboxes that are ticked once a process is complete.
I have created a query that has a calculated field, that I want to return a text value depending on what stage it is at, however the difficulty is - not all processes are required, so it needs to check the previous processes first if they are ticked to be required and then return the value.
I was getting on fine - and what I had done so far was working, however the IIf statements were starting to get very long and it then refused to let me enter any more... and subsequently found there is a limit to the length of expressions.
If anyone has any suggestions on how I could do this differently - it would be much appreciated.
Here is what I had done so far:
Ive come up against a brick wall :banghead:... and cant seem to find away around it. Any help would be greatly recieved.
Thanks, Kieran
I'm currently working on a database to track items through a manufacturing plant, so have multiple checkboxes that are ticked once a process is complete.
I have created a query that has a calculated field, that I want to return a text value depending on what stage it is at, however the difficulty is - not all processes are required, so it needs to check the previous processes first if they are ticked to be required and then return the value.
I was getting on fine - and what I had done so far was working, however the IIf statements were starting to get very long and it then refused to let me enter any more... and subsequently found there is a limit to the length of expressions.
If anyone has any suggestions on how I could do this differently - it would be much appreciated.
Here is what I had done so far:
Code:
Status: IIf([delivered]=Yes And [tobefitted]=Yes,"Fitting",
IIf([delivered]=Yes And [tobefitted]=No,"Complete",
IIf([req_60]=Yes And [60_finished]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [50_assembled]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [req_50]=No And [40_cnc]=Yes,"70 Packing And Delivery",
IIf([req_60]=No And [req_50]=No And [req_40]=No And [30_machined]=Yes,"70 Packing And Delivery",
IIf([req_50]=Yes And [50_assembled]=Yes And [req_60]=Yes And [60_finished]=No,"60 Finishing",
IIf([req_40]=Yes And [40_cnc]=Yes And [req_50]=No And [req_60]=Yes,"60 Finishing",
IIf([req_30]=Yes And [30_machined]=Yes And [req_40]=No And [req_50]=No And [req_60]=Yes,"60 Finishing",
IIf([req_40]=Yes And [40_cnc]=Yes And [req_50]=Yes And [50_assembled]=No,"50 Assembling",
IIf([req_30]=Yes And [30_machined]=Yes And [req_40]=No And [req_50]=Yes,"50 Assembling",
IIf([req_20]=Yes And [20_programmed]=Yes And [req_30]=No And [req_40]=Yes And [40_cnc]=No,"40 CNCing",""))))))))))))
Ive come up against a brick wall :banghead:... and cant seem to find away around it. Any help would be greatly recieved.
Thanks, Kieran