Transform can't do all my rules

[paulmtl]

Hi all,

I used this code to transform my output but it is not always right,

transform(iif(netamount<0,Query.cusfax,Query.trstfax),'@R (999) 999-9999 9999')

because I get :

(123) 456-7890 good for the input as 1234567890

(456) 789-0 wrong for the input as 4567890, supposed to have ( ) 456-7890

To correct the phone number format for the report, the following format rule are applied:

Case Format Number as entered Printed as Condition

1 1AAANNNNNNN 14165551212 (416) 555-1212 If leading ‘1’

2 AAANNNNNNN 4165551212 (416) 555-1212 If length is 10 and no leading ‘1’

3 NNNNNNN 5551212 ( ) 555-1212 If length is 7and no leading ‘1’

4 NNNNNNNNNNNN 011915551212 011915551212 Does not match any of the above


In case 1, the report checks for a leading ‘1’. In case 2, when there is no leading ‘1’, the report checks if the length equals 10 digits. Case 4 is the default when the numbers does not follow the any of the patterns.

I would to have a single function to check for all above cases if it possible

Any help in coding would be appreciated ?
TIA

 

[rgbean]

Paul,
One way to do this is to use a case statement and make the exact checks you've listed.

lcPhone = ALLTRIM(iif(netamount<0,Query.cusfax,Query.trstfax))
lcFormat = replicate("9", 20) && 20 is the max length
DO CASE
CASE Left(lcPhone, 1) = "1" AND LEN(lcPhone)=11
   lcPhone = SUBSTR(lcPhone,2)
   lcFormat = "@R (999) 999-9999"
CASE LEN(lcPhone)=10
   lcFormat = "@R (999) 999-9999"
CASE Left(lcPhone, 1) <> "1" AND LEN(lcPhone)=7
   lcFormat = "@R (   ) 999-9999"
*OTHERWISE
ENDCASE

RETURN transform(lcPhone,lcFormat)

Rick

 

[paulmtl]

Thanks Rick,
It works fine again.
Paul