Results 1 to 4 of 4
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    NESTED DLOOKUP (ARK SR1)

    Is it possible to nest DLookup statements??
    I have two working Dlookup Statements (shown below)

    Statement 1
    =Trim(DLookUp("[HEDTEXT]","QUERY - HEADER ID 114_SECURITY CLASS"))
    - This returns a value of CPRxxxx which is the currently loaded CPR. This value changes whenever new data is loaded into the system. It only returns one value. For this example, the result is CPR0302

    Statement 2
    =DLookUp("[DATE]","TABLE - DATE SELECTION CONVERT","[CPRNAME] = 'CPR0302'")

    This statement correctly looks up the date value associated with CPR0302 and returns 200203.

    What I want to do is combine both statements such that Statement 2 looks at the results of statement 1 to get the 200203 value. I have tried the following but get an error message.

    =DLookUp("[DATE]","TABLE - DATE SELECTION CONVERT","[CPRNAME] = 'Trim(DLookUp("[HEDTEXT]","QUERY - HEADER ID 114_SECURITY CLASS"))'")

    Is this possible??? Can anyone think of another way to do this??? Would anyone in their right mind do this?

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: NESTED DLOOKUP (ARK SR1)

    Hi Gary,
    Short answer is yes you can:
    =DLookUp("[DATE]","TABLE - DATE SELECTION CONVERT","[CPRNAME] = '"&Trim(DLookUp("[HEDTEXT]","QUERY - HEADER ID 114_SECURITY CLASS"))&"'")
    (air code so you'll need to check the parentheses etc.!)
    Not sure how efficient it will be but it should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: NESTED DLOOKUP (ARK SR1)

    Thanks Rory,

    I will give this a try. I will post the working code should I get it to work.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: NESTED DLOOKUP (ARK SR1)

    The following worked (just as you indicated)
    =DLookUp("[DATE]","TABLE - DATE SELECTION CONVERT","[CPRNAME] = '" & Trim(DLookUp("[HEDTEXT]","QUERY - HEADER ID 114_SECURITY CLASS")) & "'")
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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