How to pull a dlookup into a query field

[waldemar]

I have sort a join Problem between two tables. The difference to regular joins is that I dont want to join the rows, but two or three field entries...

Two Tables:

Table A contains fields (not rows) listing intCodeIDx, intCodeIDy, intCodeIDz.
Table B holds a detailed field for each of these CodeIDs: strDescription

I want to create a query, that is based on Table A, but also pulls the Description from Table B:
intCodeIDx, strDescriptionOf(intCodeIDx), intCodeIDy, strDescriptionOf(intCodeIDy), intCodeIDz, strDescriptionOf(intCodeIDz)

I think this might work with a DLookup inside the query, but I get an error using this as the query field:

Ausdr1: DomWert("[strDescription]";"[tblCodeDescriptions]";"intCodeID=" & [intCodeIDx])
(DomWert is the german QBD Access term for DLOOKUP) ^^^^^^^^^^^^^^^^^^^^^^ this syntax I am not sure about

Does anyone know of a solution?

 

[waldemar]

Well, actually, that Code works with one exception: Whenever there is no intCodeID available (Null) the query returns ugly #ERROR fields and pops up an error message when one points the mouse pointer on that field...

So I guess right now I am looking for an NZ() equivalent inside SQL or DOMAIN AGREGATE FUNCTIONS... Wrapping that NZ() around the Lookup should solve the problem, right? Anybody a clue?

 

[waldemar]

As for the query field:

strDescription: DomWert(&quot;[strDescription]&quot;;&quot;[tblCodeDescriptions]&quot;;&quot;[intCodeID]=&quot; & Wenn([intCodeIDx]<>&quot;&quot;;[intCodeIDx];0))

(DomWert=Dlookup, Wenn=If)

seems to cancel out the missing ones errors. But now I get errors if (=wenn) intCodeIDx was found (and is not &quot;&quot...

 

[waldemar]

strDescription: DomWert(&quot;[strDescription]&quot;;&quot;[tblCodeDescriptions]&quot;;&quot;[intCodeID]=&quot; & Wenn(IstNull([intCodeIDx]);0;[intCodeIDx]))

does the job