SQL Server (starting with 2012) also has the IIF() function. The syntax is
IIF(condition, value_if_true, value_if_false)
I think the DLookup Function in Access is equivalent to the SELECT statement in SQL Server.
DLookUp("OtherCapabilityDesc","Capability With RAG","QTRID = '')
SELECT OtherCapabilityDesc FROM [Capability With RAG] WHERE QTRID = '';
SELECT IIF (r.[Other1] IS NULL, NULL, r.OtherCapabilityDesc) AS [Other1 - Name],
nr.[Other1],
fr.Other1 AS [Other1 - Future]
FROM [vwCapability With RAG] AS r
INNER JOIN [vwCapability Now with RAG_Crosstab] AS nr
ON r.QTRID = nr.QTRID AND r.ProjReturnID = nr.ProjReturnID
INNER JOIN [vwCapability Future with RAG_Crosstab] AS fr
ON r.QTRID = fr.QTRID AND r.ProjReturnID = fr.ProjReturnID
WHERE r.Capability = 'Other1';