Add an additional Variant to the iif statment

[Ian Frost]

Below is the code for determining the Point of Contact on a project during a specific status of the project (Planning, Design, Construction). Once the status of the project is in construction "10" the POC is role3 - Project Manager. My issue is with 4 - Planning and 8 - Design. When the project is in planning the POC is role1 - Planner and in design the POC is role2 - Architect in Charge. The issue is that some projects in Planning or Design do not have a POC related to the status, in that instance I want the POC to be role3 - Project Manager. I have highlighted the two code lines that I am having problems with.

Thank You for your help.

Function PointOfContact(mproject, mstatus, mtype)
' mproject = fp&d project #
' mstatus = project status
' mtype = project type
Dim mpoc As Variant, role1 As Variant, role2 As Variant, role3 As Variant
mpoc = Null
role1 = "Planner"
role2 = "Architect in Charge"
role3 = "Project Manager"
role4 = "FPD Rep"

Select Case mstatus

Case Is = 1


mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role3 & Chr(34))
'mpoc = IIf(Not IsNull(mpoc), mpoc & ", " & role3, "")

Case Is = 4
mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role1 & Chr(34))
'mpoc = IIf(Not IsNull(mpoc), mpoc & ", " & role1, "")

Case Is = 8
mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role2 & Chr(34))
'mpoc = IIf(Not IsNull(mpoc), mpoc & ", " & role2, "")

Case Is = 10
mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role3 & Chr(34))
'mpoc = IIf(Not IsNull(mpoc), mpoc & ", " & role3, "")

[CJ_London]

Something like this?

Case Is = 4
mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role1 & Chr(34))
if isnull(mpoc) then mpoc = DLookup("[member name]", "team_members_with_roles", "[fp&d project #] = " & Chr(34) & mproject & Chr(34) & " and [role] = " & Chr(34) & role3 & Chr(34))

[ranman256]

1. you CAN use single quotes and it is easier to read:
mpoc = "[fp&d project #] = ' " & mproject & " ' and [role] = ' " role1 & " ' ") ' no spaces around quotes, these are for readability

2. note , params usually get the prefix p, as parameter inside the function, so they dont confuse with modular variables m.
Function PointOfContact(pProject, pStatus, pType)

[Ian Frost]

Ajax, Thanks the code works perfect.