Can someone please help me with the rewrite of the IIF and Dlookup I this code . I understand better with CASE ad Select statements
SELECT
ScopeHistory.ProjectReturnID,
ScopeHistory.ProjectID,
--IIf([ScopeDate] Is Null,Null,
--DLookUp("QTR_ID","Quarters","QuarterNo = " & IIf(Month([ScopeDate])<4,4,IIf(Month([ScopeDate])<7,1,
--IIf(Month([ScopeDate])<10,2,3))) & "AND CalendarYear = " & Year([ScopeDate])))*1 AS [QTR Change Occurs],
--IIf([Qtr Change Occurs]=[QtrID],1,0) AS [Change This Qtr], Month([ScopeDate]) AS test,
FROM
(((((ScopeHistory
INNER JOIN Impact ON ScopeHistory.ImpactEndDate = Impact.ImpactID) INNER JOIN Impact AS Impact_1 ON ScopeHistory.ImpactCosts = Impact_1.ImpactID)
LEFT JOIN Impact AS Impact_2 ON ScopeHistory.ImpactStratOut = Impact_2.ImpactID)
LEFT JOIN Impact AS Impact_3 ON ScopeHistory.ImpactStratOutNM = Impact_3.ImpactID) LEFT JOIN Impact AS Impact_4 ON ScopeHistory.ImpactStratOutM = Impact_4.ImpactID)
LEFT JOIN Impact AS Impact_5 ON ScopeHistory.ImpactPeople = Impact_5.ImpactID
ORDER BY ScopeHistory.ProjectReturnID;