Dlookup nested in IiF

[lawtonl]

I've had a quick search but couldn't find anything specific to my query.

I'm trying to run a query but if one of the fields from the query returns null I want it to check a field in another table and populate it with that value if 2 dates match.

I have this as an expression in the query:

New: IIf([New Tariff] Is Null,DLookUp("[Gprs Tariff]","[MobileDataDump Sheet 1]","#[MPN Level]![Ordered Date]#"="#[MobileDataDump Sheet 1]![Contract Start Date]#"),[New Tariff])

I'm not sure whether I'm going about this the right way so any advice is appreciated.

tia

 

[pbaldy]

For starters, the syntax of the DLookup is off:

http://www.mvps.org/access/general/gen0018.htm

Also, I'd use the Nz() function rather than the IIf() (though either should work).

 

[lawtonl]

I've updated the code using the correct syntax, I think. I've used iif as I'm not familiar with nz().

At the moment it's not actually finished running, it seems to take an age.

 

[pbaldy]

Well, a DLookup in a query is usually a bad idea. They are notorious for slowing them down. Can the tables be joined instead?