Phone Number Format

[DS_MJ]

Hello:

Can somebody help with the below query. It works fine.
BUT shows ( ) - EXT if Null

I need it to NOT SHOW ( ) - EXT if these fields are blank or null

Code: Select all

IF               Isnull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) = '000'
          AND Isnull(Lnk_Cpy_Ds.Ph2) OR (Lnk_Cpy_Ds.Ph2) = '000'
          AND Isnull(Lnk_Cpy_Ds.Ph3) OR (Lnk_Cpy_Ds.Ph3) = '000'
          AND Isnull(Lnk_Cpy_Ds.WRKEXT) OR (Lnk_Cpy_Ds.WRKEXT) = '000'  
THEN ''  

    ELSE IF   IsNotnull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) <> '000'
          AND IsNotnull(Lnk_Cpy_Ds.Ph2) OR (Lnk_Cpy_Ds.Ph2) <> '000'
          AND IsNotnull(Lnk_Cpy_Ds.Ph3) OR (Lnk_Cpy_Ds.Ph3) <> '000'
          AND IsNotnull(Lnk_Cpy_Ds.WRKEXT) OR (Lnk_Cpy_Ds.WRKEXT) <> '000'  
     THEN '(' : Trim(Lnk_Cpy_Ds.Ph1): ')' : Trim(Lnk_Cpy_Ds.Ph2) : '-' : Trim(Lnk_Cpy_Ds.Ph3) :' EXT. ' : Trim(Lnk_Cpy_Ds.WRKEXT)

		 ELSE IF  IsNotnull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) <> '000'
			  AND IsNotnull(Lnk_Cpy_Ds.Ph2) OR (Lnk_Cpy_Ds.Ph2) <> '000'
			  AND IsNotnull(Lnk_Cpy_Ds.Ph3) OR (Lnk_Cpy_Ds.Ph3) <> '000'
			  AND Isnull(Lnk_Cpy_Ds.WRKEXT) OR (Lnk_Cpy_Ds.WRKEXT) = '000'  

		 THEN '(' : Trim(Lnk_Cpy_Ds.Ph1): ') ' : Trim(Lnk_Cpy_Ds.Ph2) : '-' : Trim(Lnk_Cpy_Ds.Ph3)

			 ELSE IF     IsNull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) = '000'
				  AND IsNotnull(Lnk_Cpy_Ds.Ph2) OR (Lnk_Cpy_Ds.Ph2) <> '000'
				  AND IsNotnull(Lnk_Cpy_Ds.Ph3) OR (Lnk_Cpy_Ds.Ph3) <> '000'
			 THEN Trim(Lnk_Cpy_Ds.Ph2) : '-' : Trim(Lnk_Cpy_Ds.Ph3)

				ELSE IF       IsNull(Lnk_Cpy_Ds.Ph1)    OR (Lnk_Cpy_Ds.Ph1) = '000'
						  AND IsNull(Lnk_Cpy_Ds.Ph2)    OR (Lnk_Cpy_Ds.Ph2) = '000'
						  AND IsNotnull(Lnk_Cpy_Ds.Ph3) OR (Lnk_Cpy_Ds.Ph3) <> '000'
					 THEN Trim(Lnk_Cpy_Ds.Ph3)
						  ELSE
                              'INVALID PHONE NUMBER'

Thanks

[chulett]

Build the pieces with separate expressions concatenated together:

Code: Select all

Isnull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) = '000' Then '' Else '(' : Trim(Lnk_Cpy_Ds.Ph1): ') ' : ( handle phone 2) : ( handle phone 3 )

[DS_MJ]

Build the pieces with separate expressions concatenated together:

Code: Select all

Isnull(Lnk_Cpy_Ds.Ph1) OR (Lnk_Cpy_Ds.Ph1) = '000' Then '' Else '(' : Trim(Lnk_Cpy_Ds.Ph1) ...[/quote]

Thank you.
I will try it.