Query expression string too long

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to type the following expression into my query and keep getting
the msg that query string is too long. Can someone help me shorten it?
Thanks

Status1Parent: IIf([Status]="6",
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartOne] & "' AND [Status] = '1'") Is
Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartOne] & "' AND [Status] = '1'"),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartTwo] & "' AND [Status] = '1'") Is
Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartTwo] & "' AND [Status] = '1'"))),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartThree] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartThree] & "' AND [Status] = '1'"),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartFour] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartFour] & "' AND [Status] = '1'"))),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartFive] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartFive] & "' AND [Status] = '1'"),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartSix] & "' AND [Status] = '1'") Is
Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartSix] & "' AND [Status] = '1'"))),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartSeven] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartSeven] & "' AND [Status] = '1'"),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartEight] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartEight] & "' AND [Status] = '1'"))),
IIf(DLookUp("[LTD]","NQ","[CP] = '" & [PartNine] & "' AND [Status] = '1'")
Is Not Null,
DLookUp("[LTD]","NQ","[CP] = '" & [PartNine] & "' AND [Status] = '1'"),
[LTD]))))))
 
Normalize your table structure and use links instead of all of the IF's and
DLOOKUP's.
 
Back
Top