Combining values in an expression using IIf

[BonnieG]

Hello all

I'm trying to create a text box on a form which displays a value based on numerous criteria.

Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.

I have two fields which need to be pulled into this expression:

is_leaver - text box, with value either "Yes" or NULL
organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd

The text box I have is called employee_status

I can see that there are 4 possible combinations:

1. Internal Employee - Is an internal employee and still works here
2. Internal Ex-Employee - Is an internal employee but has left
3. External Employee - Is an external employee who still works here
4. External Ex-Employee - Is an external employee who has left

So far I've managed this:

=IIf(IsNull([is_leaver]),"Employee","Ex-Employee")

This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?

Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.

Thanks in advance for any help.

 

[pr2-eugin]

How do you identify they are Internal or External?

 

[BonnieG]

If the "organisation" field has the name of our organisation in it (Happy People Ltd) then they're internal. If not, they're external - we have about 20 other organisations who use our systems and they're all in the same database.

 

[pr2-eugin]

So how about this,

= IIF(organisation = "Happy People Ltd", "Internal ", "External ") & _
  IIf(IsNull([is_leaver]),"Employee","Ex-Employee")

 

[BonnieG]

THANK YOU Paul! As always you have been sooo helpful. I owe you one!

 

[pr2-eugin]

No problem ! You're welcome.