Null in DLookup Criteria (1 Viewer)

sneuberg

AWF VIP
Local time
Today, 07:44
Joined
Oct 17, 2014
Messages
3,506
I have an expression in a query like:

Code:
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:
Code:
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

Super Moderator
Staff member
Local time
Tomorrow, 01:44
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 07:44
Joined
Oct 17, 2014
Messages
3,506
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.
 

Attachments

  • attachment2 (2).accdb
    936 KB · Views: 78

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:44
Joined
Sep 12, 2006
Messages
15,613
can you do this?

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

Users who are viewing this thread

Top Bottom