Concatenation of three fields with conditional parenthesis around last two fields for a field in a View

Transact-SQL
1

SQLServer 2008r2
The single record view uses this:
ISNULL(dbo.APD_Stips1.Stip_Abv, N' ') + N' ' + N'(' + ISNULL(dbo.APD_Stips2.Dt_Start, N' ') + N' ' + ISNULL(dbo.APD_Stips2.Dt_End, N' ' + N')') + N')'

SQL Server 2008r2

Most of the three fields have a Stips Code, a begin period, a end period.
In most cases the code above returns something like this: BO (03/01 08/15)
In the case where there is a code, but the Begin period and End period are empty, it returns one of the two formats.
Actual example: mostly this PM ( ) and sometimes this CL ( ))

The desired output would be this: PM

In MSACCESS or a later version of SQL the IF statement would make this easy.
Later, these fields are GroupBy a common ID. It is the Parenthesis with empty values I want to just be blank
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=58892&stc=1&d=1434378453

2

Use a combination of NULLIF and ISNULL like shown below.

ISNULL(dbo.APD_Stips1.Stip_Abv, N' ') + N' '  +
ISNULL( NULLIF(
	+ N'(' + ISNULL(dbo.APD_Stips2.Dt_Start, N' ') + N' ' 
	+ ISNULL(dbo.APD_Stips2.Dt_End, N' ' + N')') + N')'
, '(  )'),'')

I may not have counted the spaces you added exactly - you have to make sure that the string in single quotes on the last line exactly matches the string you want to remove if both dbo.APD_Stips2.Dt_Start and dbo.APD_Stips2.Dt_End are null.

I don't know about MS Access, but in any version of SQL Server you can use these functions, or CASE expressions. IF construct is for flow control; to achieve similar effect in an expression use CASE expression. The combination of ISNULL and NULLIF is sort of a shortcut.

3

Thanks for the nobel attempt. I am putting a screen shot to show you they are exactly the same result.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=58903&stc=1&d=1434398710

4

I am not able to see you image/attachment. In any case, see the example below and see what is different in your query. If that doesn't help, post DDL and sample data (like I have done below)

CREATE TABLE #tmp (a VARCHAR(32), b VARCHAR(32), c VARCHAR(32));
INSERT INTO #tmp VALUES 
	('a',NULL,'y'),
	('b','x','y'),
	('c',NULL,NULL),
	('d','x',NULL);
	
SELECT
	a + ' '
	+ ISNULL( NULLIF(
		'(' + ISNULL(b,' ') + ' ' 
		+ ISNULL(c,' ') + ')'
	,'(   )'),'')
FROM
	#tmp;
	
DROP TABLE #tmp;