Replace internal spaces in string

[Former Member]

I have only recently begun using Crystal Reports 2013.  I am having a problem with eliminating errors when creating formulas.  I have a database field which is a composite of a fixed length Last Name, First Name and Middle Initial.  Because it was fixed length the last name is followed by multiple spaces prior to adding the first name.  I would like to remove all the redundant spaces and replace them with a comma and single space.  The Replace function, by itself, cannot give me what I need.  I have tried multiple times to employ a Do While or a While End While loops to to surround the Replace function, but I continue to receive the same error message of: "The keyword 'do' is missing"  Obviously, the keyword is not missing regardless of what case the statement uses.  I am at a loss to get the Do While loop to work.  I have attempted many different variations and all of them throw this same error.  The last formula I attempted looks like this:

Do While {CFTVMA2000.PrvName}.Contains("  ")

     Replace({CFTVMA2000.PrvName},"  ",", "

Loop

I realize that the full syntax may not be correct but I cannot get past the missing 'do' error!

[abhilash_kumar · Accepted solution]

Hi Ron,

What is the fixed length of the last name field?

Also, do first names and initials also have fixed lengths?

Could you paste an example string as well?

-Abhilash

[abhilash_kumar · Accepted solution]

Please try using this formula and let me know if this works:

local stringvar ss := {CFTVMA2000.PrvName};

local stringvar array arr := split(ss,' ');

local numbervar i;

local stringvar fin;

for i := 1 to ubound(arr) do

(

    if i = 1 AND (arr[i] <> ' ' or arr[i] <> '') then

        fin := arr[i] + ', '

    else if arr[i] <> ' ' or arr[i] <> '' then

        fin := fin + arr[i] + ' ';

);

fin;

-Abhilash

[Former Member · Accepted solution]

THANK YOU!  What you sent worked just fine.  I was not sure of the original field sizes were but would appear to be around 13-14 characters.  Still I have the question of why the Do While loop does not work and always states the 'do' is missing?