Combining Dlookup function

[Thompyt]

Is it possible to combine



IsComp: DLookUp("Action_Complete","[Change Request]","CR_No = " & [Change Request].[CR_No] & " AND Sub_No<>0"
SDate: DLookUp("[Date_Closed]","[Change Request]","CR_No= " & [CR_No] & " AND Sub_No=0"

Since both reside in the [Change Request] table? or does the criteria have to be the same also?

What would be the most efficient way of coding this. It slows down the report output considerably.

SELECT [Change Request].CR_ID, DLookUp("Action_Complete","[Change Request]","CR_No = " & [Change Request].[CR_No] & " AND Sub_No<>0") AS IsComp, DLookUp("[Status 1]","[Switching]","CR_No = " & [CR_No]) AS Same, DLookUp("[Date_Closed]","[Change Request]","CR_No= " & [CR_No] & " AND Sub_No=0") AS SDate, IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01,"    " & " .00")) AS CR_Numberz, IIf(([IsComp] And [Action_Complete]=False) Or ([Same]<>[Status 1]) Or ([Sdate]<>[Date_Closed] And [Action_Complete]=False),[CR_No] & Trim([CR_Numberz]),[CR_Numberz]) AS CR_Numbers, Vote_Switch.[Status 1]
FROM [Change Request] INNER JOIN Vote_Switch ON [Change Request].CR_ID = Vote_Switch.CR_ID;

[CJ_London]

the domain functions (DLookup etc) can only return one value and should not really be used in queries.

why can't you just bring the change request table into your query, linking on CR_No?