Need to concatenate area Code and phone number field and format it (i.e) 812-424-4444

[BDAIS]

Below is the formula I have but it does not work. I keep getting the following error: A Colon is expected here and it highlights formula. The two fields are numbers.

If there is a better way to do this, I am open to suggestions.


numbervar area;
numbervar phone;
numbervar num;
numbervar phLength;

area = {CMASTR.CU_ARE};
phone = {CMASTR.CU_PHN};
num = tonumber(totext(area,0,"") + totext(phone,0,""));
phLength = len(totext(num,0,""));

Select phLength
Case '7'
formula: = left(totext(phone,0,""),3) + "-" + right(totext(phone,0,""),4)
Case '10'
formula: = "(" + totext(area,0,"") + ") " + left(totext(phone,0,""),3) + "-" + right(totext(phone,0,""),4)
Default
formula: = ""
End Select

 

[MCuthill]

BDAIS,

I am not 100% sure why you are getting the error, though it could be that "formula" is not defined in your variables. In any event, I think the following should return what you are seeking though I have not been able to test your exact method in Crystal Reports to guarantee. (I used a simple sample list of 7 and 10 digit numbers for testing, not building off of two fields)

{@Format_Phone}

numbervar area;
numbervar phone;
numbervar num;
numbervar phLength;

area = {CMASTR.CU_ARE};
phone = {CMASTR.CU_PHN};
num = tonumber(totext(area,0,"") + totext(phone,0,""));
phLength = len(totext(num,0,""));

IF phLength = 7 THEN
(
    Picture (CStr(num)"xxx-xxxx");
)
ELSE
IF phLength = 10 THEN
(
    Picture (CStr(num), "(xxx) xxx-xxxx");
)
ELSE
(
    ""
)

As an aside, I am unsure why you are converting "num" to be a number, though converting it back to a string for formatting. If you re-define "num" as a StringVar, you may be able to skip some of the back and forth type-coversions immediately before your CASE statement.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

oops.

I missed a comma when editing for your variable names in place of my fields, please see below.

numbervar area;
numbervar phone;
numbervar num;
numbervar phLength;

area = {CMASTR.CU_ARE};
phone = {CMASTR.CU_PHN};
num = tonumber(totext(area,0,"") + totext(phone,0,""));
phLength = len(totext(num,0,""));

IF phLength = 7 THEN
(
    [red]Picture (CStr(num), "xxx-xxxx");[/red]
)
ELSE
IF phLength = 10 THEN
(
    Picture (CStr(num), "(xxx) xxx-xxxx");
)
ELSE
(
    ""
)

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Crystal syntax:

stringvar ph;
if isnull({CMASTR.CU_PHN}) then
ph [red]:[/red]= "" else
if isnull({CMASTR.CU_ARE}) then
ph [red]:[/red]= totext({CMASTR.CU_PHN},0,"") else
ph [red]:[/red] = totext({CMASTR.CU_ARE},0,"")+totext({CMASTR.CU_PHN},0,"");
if len(ph)=0 then
"" else
IF len(ph) = 7 THEN
Picture(ph,"xxx-xxxx") ELSE
IF len(ph) = 10 THEN
Picture (ph, "(xxx) xxx-xxxx")

-LB