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 ( ))
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.
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;