DSXchange

Posted: **Fri Jun 07, 2013 11:14 am**

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

Posted: **Fri Jun 07, 2013 3:24 pm**

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 )

Posted: **Fri Jun 07, 2013 4:54 pm**

chulett wrote: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.

DSXchange

Phone Number Format

Phone Number Format