Combining Dlookup functions

[Thompyt]

I have the following in a Query (Switching)



CR_Number: Format(([CR_No]+([Sub_No]*0.01)),"Fixed")

CR_Numberz: IIf([SUB_No]=0,Format([Change Request].[CR_No],"#"),Format([Sub_No]*0.01," " & " .00"))

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

CR_Numbers: IIf([IsComp] And [sub_no]<>0 And [Action_Complete]=False,[CR_No] & Trim([CR_Numberz]),[CR_Numberz])

I was also looking into adding the following. (It slows the process extremely)

Same: DLookUp("[Status 1]","[Chng_ReqQry]","CR_No = " & [CR_No] & " AND Sub_No<>0") , it is a roundabout way since Status 1 is from the same Query Switching and then inserting outcome into CR_Numbers as:

CR_Numbers: IIf([IsComp] And [Action_Complete]=False Or Same <> Status 1 And Action_Complete=True ,[CR_No] & Trim([CR_Numberz]),[CR_Numberz])

This will work until I restart the database, then it locks up. I have to remove the Or Same <> [Status 1] from CR_Numbers.

Is there an easier way to combine?

[June7]

You are mixing OR and AND logical operators. Parentheses are critical.

Literal text needs delimiters.

Do you mean:

CR_Numbers: IIf(([IsComp] And [Action_Complete]=False) Or (Same <> "Status 1" And Action_Complete=True), [CR_No] & Trim([CR_Numberz]), [CR_Numberz])

[Thompyt]

Thanks June
It does work on that Query - Switching. But is real slow and if I run a report using this, I get an unknown error multiple times. Enough I have to use the Task manager to kill Access.

[June7]

Yes, domain aggregates can perform slowly in queries and textboxes.

Perhaps need to look at revising data structure so you can have a join to retrieve the related info.

[Thompyt]

June,
Fixed the slowness. I shortened the Same Code to:

Same: DLookUp("[Status 1]","[Chng_ReqQry]","CR_No = " & [CR_No])
CR_Numbers: IIf(([IsComp] And [Sub_no]<>0 And [Action_Complete]=False) Or (Same<>[Status 1]),[CR_No] & Trim([CR_Numberz]),[CR_Numberz])