DLookup within Iif statement

[Walter]

I'm having trouble getting an Iif Statement that includes a Dlookup to work.
Here is what I have but the records where the Iif is true show nothing for
this field.
IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate
<=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price])
Thanks!
Walter

 

[Jeanette Cunningham]

Walter,

there is a typo here:
[qryNetRevenue].![Loadeddate]
you have both a . and a !

Maybe you need to remove the ! from
[qryNetRevenue].![Loadeddate]


If the above doesn't work, please post back with more details of what you
are trying to do.

Jeanette Cunningham

 

[fredg]

I'm having trouble getting an Iif Statement that includes a Dlookup to work.
Here is what I have but the records where the Iif is true show nothing for
this field.
IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate
<=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price])
Thanks!
Walter

All arguments in a DLookUp must refer to the same table. You can't
lookup a value in tblProductCost using criteria from qryNetRevenue.
Perhaps you can use a DlookUp within the DLookUp.
Off the top of my head, try:

=IIf([FreightOnly]=False,[Price]-DLookUp("Price","tblProductCost","EffectiveDate
<=#" & DLookUp("[Loadeddate]","qryNetRevenue") & "#")
,[tblOrders].[Price])
The above assumes qryNetRevenue returns only one record, so no
additional criteria is needed.