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'