Null in DLookup Criteria

[sneuberg]

I have an expression in a query like:

PV: DLookUp("[Position_Value]","[Table2]","[Position_Date] = #" & [PD] & "#")

If [PD] is Null this expression produces an error. I need it to return Null. Currently I use Nz like:

PV: DLookUp("[Position_Value]","[Table2]","[Position_Date] = #" & Nz([PD],"1/1/00") & "#")

to put in a date that's valid but not in the table. But this is an ugly hack. Please tell me there's a more elegant way of doing this.

 

[Galaxiom]

It is an ugly expression but it works. Users won't see it anyway.

The way you have written it suggests that it used to derive a field in a query. Are you sure you can't do this with a join to Table2? Would be far more efficient.

 

[sneuberg]

Are you sure you can't do this with a join to Table2? Would be far more efficient.

I don't think so but I've attached the database in which I'm using this sort of expression in case you want to look. I'm trying to help a forum member in this thread with a requrement that needs to retrieve data in another record in the same table. I think I finally was able to create a subquery that does what he wants but it's pretty slow. I was exploring using DMax and DLookup to use in place of the subquery. I was a bit surprised that the Rube Goldberg combination of DMex and DLookup is actually quite a bit faster than the subquery.

 

[gemma-the-husky]

can you do this?

iif (isnull(PD),null,dlookup(etc)))

 

[sneuberg]

can you do this?

iif (isnull(PD),null,dlookup(etc)))

That's exactly what I needed to do. Thanks.