Combine Iif and Dlookup in function

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

Guest

I have the following expression in a query that's giving me an error msg.
that the string is too long. I'm hoping I can rewrite this into a function
and then put the function in the query. But, I'm not sure how the write a
function that imulates the following expression. If someone could get me
started I'd really appreciate it. Thank you.

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'"),[LTD])))
 
Try this as your query's calculated field's expression:

Status1Parent: IIf([Status]="6",Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartOne] & "' AND [Status] = '1'"), Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartTwo] & "' AND [Status] = '1'"),Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartThree] & "' AND [Status] = '1'"),Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartFour] & "' AND [Status] = '1'"))))))
 
Perfect - thanks so much!

Ken Snell said:
Try this as your query's calculated field's expression:

Status1Parent: IIf([Status]="6",Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartOne] & "' AND [Status] = '1'"), Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartTwo] & "' AND [Status] = '1'"),Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartThree] & "' AND [Status] = '1'"),Nz(DLookUp("[LTD]","NQ","[CP] = '" &
[PartFour] & "' AND [Status] = '1'"))))))

--

Ken Snell
<MS ACCESS MVP>


Alex said:
I have the following expression in a query that's giving me an error msg.
that the string is too long. I'm hoping I can rewrite this into a
function
and then put the function in the query. But, I'm not sure how the write a
function that imulates the following expression. If someone could get me
started I'd really appreciate it. Thank you.

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'"),[LTD])))
Click to expand...
Click to expand...
 
Back
Top