Setting a field value based on multiple possible conditions in Access

Question

Hi all,

First time posting and quite new to Access so will do my best to give all relevant information but let me know if I've missed something!

We are running a database to manage the return of components, covering raising a case, arranging collection, raising a job card, taking the part apart, recording our findings, conducting a review and closing the case out.

Multiple teams are involved in this process so there is a single continuous form that brings all the key data together in a summary. The first column in that form is a text box called STATUS, and has a conditional value that is displayed and updated depending on multiple possible conditions, by means of an IF statement in the form query;

Status: IIf([ReqCancelled]="Yes","F99",IIf([JobCardCancelledDate]>0,"F98",IIf([JobCardClosedDate]>0,"F97",IIf([DateReviewCompleted]>0,"F50",IIf([DateReviewStarted]>0,"F45",IIf([DateTDCompleted]>0,"F35",IIf([DateTDStarted]>0,"F25",IIf([InArea]=True,"F20",IIf([DateRequestedFromStores]>0,"F15",IIf([JobCardDate]>0,"F12",IIf([TD06PRR].[DateReqReceived] Is Not Null,"F10",IIf([TD06Req].[ReqNo] Is Not Null,"F03",IIf([TD06Req].[RequestDate] Is Not Null,"F02","F01")))))))))))))

I recognise it's a messy solution involving a LOT of IF statements in this, 13 at the moment! But it has worked reliably for 3 years so I haven't tinkered with it!

However, there are a number of bottlenecks in the process and we want to split down some of the status' into smaller groups, there are an additional 5 conditions identified so far but there are likely to be more needed.

Clearly the current method will not accommodate these additional conditions, trying to add more just raises an error message saying the expression is too complex.

Can anyone offer a better solution that not only will accommodate the new conditions but is open to being increased please? Ideally without too much in the way of VBA coding as that is not one of my strengths!

If you need any more information just let me know.

Many thanks

Answer 1

Ok, the nested IIF covers several different field values. So a table would not be practical.

I would use a VBA module. somethng like this:

Public Function fStatus as Text

If [ReqCancelled]="Yes" Then

 fStatus = "F99"

Else If [JobCardCancelledDate]>0 Then

 fStatus = "F98",

Else If [JobCardClosedDate]>0 Then

 fStatus = "F97"

etc.

Answer 2

1,switch

2,vba

if https://docs.microsoft.com/en-us/office/vba/Language/reference/user-interface-help/ifthenelse-statement

3,create a new table with fields enumerate all status combination and status then using join to search status.

new table columns like below

ReqCancelled JobCardClosedDate ..... status

select old.*,status from old join new on old.JobCardClosedDate=old.JobCardClosedDate and .....