checking length and triming the value at ','!

Hi all!

I have a record coming from the database having of F_name,L_name, and M_name being concatenated and separated with comma. In an expression of a textbox control value I would like to check the length of this record and if it is more than 12 only to take the first values till the comma’,’ which appears prior to the 12^th character and truncate the rest. For example:

Record from the database= FirstName,LastName,MiddleName

1) GAUTHIER, JEFFREY, Jane Desired result GAUTHIER, b/c if Last name is added it will be more than 12.

2) Jane, JEFFREY, GAUTHIER Desired result Jane, b/c if Last name and first name are added it will be more than 12.

3) JEFFREY, Job, GAUTHIER Desired result JEFFREY, Job, b/c if First name is added it will be more than 12.

4) JEFFRE, Job, G Desired result JEFFREY, Job, G etc b/c all the values are not more than 12

So would you mind helping me on how the expression looks like?

Thank you in advance!

Try this expression:

=Iif(Fields!name.Value.ToString().Length() > 12, Iif(Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",") <> -1, Fields!name.Value.ToString().Substring(0, Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",")), Fields!name.Value.ToString().Substring(0, 12)), Fields!name.Value)

hehe, it's big and ugly but it does what you want. You could use embedded code to make it prettier.

|||

Hi Ryan,

That doesn't work if the three names' total length is less than 12. I just ran it on my name database and some come up as #error#.. Like "Hat, Jo, Ty" ...

pl

|||

Ugh...You're right. Ok, try this instead, add the following embedded code to your report:

Function TrimWithCommas(ByVal name As Object) As String

if (name is Nothing) then
TrimWithCommas = Nothing
end if

Dim nameString as String = name.ToString()
if (nameString.Length() > 12) then
Dim index as Integer = nameString.SubString(0, 12).LastIndexOf(",")
if (index <> -1) then
nameString = nameString.SubString(0, index)
else
nameString = nameString.SubString(0,12)
end if
end if
TrimWithCommas = nameString
End Function

Then use the following expression in the textbox you want the name to appear in:

=Code.TrimWithCommas(Fields!name.Value)

|||

Hi Ryan,

Excellent! That worked on 17000 employees! No errors!

Thanks for the lesson.

pl

|||

Hi,

I've been trying to write this up in the Expression editor, and, if the first 12 bytes of the name does not contain a comma, it gives an error. Here is the code:

=Iif( len(Fields!EMPLOYEE_NAME.Value) > 12,
Iif( instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12 ), ",",12) > 0,
left(Fields!EMPLOYEE_NAME.Value, instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12), ",",12 ) - 1 ) ,
left(Fields!EMPLOYEE_NAME.Value, 12)),
left(Fields!EMPLOYEE_NAME.Value,12))

It seems that the true and false clauses are evaluated every time, and the case where the "instrrev" fails (evaluates to zero), causes the whole condition to die...

I'm just starting to learn. Did I miss something?

Thanks.