Nested IIf

[diofree]

Hi guys,
I have a simple IF statement that combines First with Last names:

IIf([PositionIncumbentLastName]<>"",[PositionIncumbentLastName] & ", " & [PositionIncumbentFirstName],"Vacant")

How would I modify this to check if Last Name was empty, or First Name was empty, to only take the other half of the name and not add the ", "

In other words, new IIF would
-if last and first are filled, change to "Last, First"
-if nothing is present, change to Vacant
-If only last is present, change to "Last"
-If only first is present, change to "First"

Thanks!

 

[plog]

Since this is VBA, I would use a variable and set that to "Vacant" by default. Then work through all your permutations for First/Last. psueduo code would be like this:

Occupant = "Vacant"
IF Length(First & " " & Last) > 1 THEN
--IF Last Isn't Null Occupant=Last
--IF First Isn't Null THEN
----IF Occupant<>"Vacant" Then Occupant+=", "
----Occupant+=First
----END IF
--END IF

Occupant now has what you want.

 

[arnelgp]

IIf(IsNull(((PositionIncumbentLastName + ", ") & PositionIncumbentFirstName)), "Vacant", IIf(InStr(((PositionIncumbentLastName + ", ") & PositionIncumbentFirstName) & "", ",") = Len(((PositionIncumbentLastName + ", ") & PositionIncumbentFirstName)) - 1, Trim(Replace(((PositionIncumbentLastName + ", ") & PositionIncumbentFirstName) & "", ",", "")), ((PositionIncumbentLastName + ", ") & PositionIncumbentFirstName)))

 

[arnelgp]

i tried re-creating the table with same fields but i did not encounter error here is the sql:

SELECT IIf(IsNull((([PositionIncumbentLastName]+", ") & [PositionIncumbentFirstName])),"Vacant",IIf(InStr((([PositionIncumbentLastName]+", ") & [PositionIncumbentFirstName]) & "",",")=Len((([PositionIncumbentLastName]+", ") & [PositionIncumbentFirstName]))-1,Trim(Replace((([PositionIncumbentLastName]+", ") & [PositionIncumbentFirstName]) & "",",","")),(([PositionIncumbentLastName]+", ") & [PositionIncumbentFirstName]))) AS Expr
FROM Table6;

 

[diofree]

Got it working in SQL. In the calculated I got it working using

IIf(IsNull([PositionIncumbentLastName]) and IsNull([PositionIncumbentFirstName]),"Vacant",IIF(Isnull([PositionIncumbentLastName]) or IsNull([PositionIncumbentFirstName]),trim([PositionIncumbentLastName] & [PositionIncumbentFirstName]),[PositionIncumbentLastName] & ", " & [PositionIncumbentFirstName]))

for some reason if i tried using ="" it didn't run... ???

thanks for the help both of you!

 

[arnelgp]

please see the attached db for a working sample