Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2001
    Posts
    45

    Unanswered: How to pull a dlookup into a query field

    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?

  2. #2
    Join Date
    Nov 2001
    Posts
    45
    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?

  3. #3
    Join Date
    Nov 2001
    Posts
    45
    As for the query field:

    strDescription: DomWert("[strDescription]";"[tblCodeDescriptions]";"[intCodeID]=" & Wenn([intCodeIDx]<>"";[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 "")...

  4. #4
    Join Date
    Nov 2001
    Posts
    45
    strDescription: DomWert("[strDescription]";"[tblCodeDescriptions]";"[intCodeID]=" & Wenn(IstNull([intCodeIDx]);0;[intCodeIDx]))

    does the job

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •