String manipulation in IIF statement

[acessn]

In my details section of my report I need to:

IIF(Employeeid>0,Fields!PersonFullname,"Open Shift")

Works like a charm.

However, the personfullname column is stored with "Lastname, Firstname", while the report requirement is to display "Firstname Lastname".

This I have solved like this:

IIF(Fields!EMPLOYEEID.Value>0,
(
Right(Fields!PERSONFULLNM.Value,(Len(Fields!PERSONFULLNM.Value)-Instr(Fields!PERSONFULLNM.Value,",")-1)
)
& " " &
Left(Fields!PERSONFULLNM.Value,Instr(Fields!PERSONFULLNM.Value,",")-1)
)
,"<Open shift>"
)

This works fine if it's an actual shift and the personfullname has a value.
If it's an open shift with employeeid=0, it displays #Error.
In the dataset, personfullname has no value if employeeid=0.

The error log shows "The Value expression for the textbox ‘PERSONFULLNM’ contains an error: Argument 'Length' must be greater or equal to zero." at report object "PERSONFULLNM"(Which is the textbox I'm displaying the string in).

Seems like it's trying to calculate the true portion of the IIF statement, even though it only should display the false statement.

Ideas?

Thanks,
Bjorn

 

[acessn]

Solved it by removing -1 from the formula, and instead wrapped a Replace(String,",","") around it.