Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dlookup in Queries (A2K/Win2k)

    I am trying to use a dlookup function inside a query. It looks like this:

    TSAssign: DLookUp("[tech_name_last]","technicians","[tech_id]= " & DLookUp("[incident_ts_assignment]","incidents"))

    The tech_id and incident_ts_assignment fields are like fields. What I want to do is lookup the last name of the tech from the technicians table. The query runs but it returns nothing in that field. Does anyone have any idea what could be causing my problem? Maybe there is a better way to do it.

    Thanks in advance,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    The "inner" DLookup has no where-condition:

    DLookUp("[incident_ts_assignment]","incidents")

    so it will not return a specific incident_ts_assignment. You might get a result if you add the relevant where-condition, but...

    It's probably better to add the technicians and incidents tables to the query, create the appropriate joins (if they haven't been created automatically by Access), and then add the tech_name_last field from the technicians table to the query grid. Using joined tables in a query is more efficient than using DLookup.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    Thanks Hans,
    All the required tables are there and joined. How do I reference tech_name_last (a text field) using tech_id (a number field)? The problem is that the tech name last field is used also somewhere else in the query that shows the record originator. The incident_ts_assignment is the person that currently owns the record. I could drop the tech_name_last in the query, but then how would you filter the output for that field?

    Thanks,
    Mark

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    I'm a little suspect of a situation that relies on an a double embedded dlookup like yours. First of all, your performance will be lousy.

    Because your second DLookup has no WHERE clause, you can't predict what ID it will return. You should, however, get something returned from the first DLookup, although it may not relate to what you want. Are you sure [incident_ts_assignment] is the tech_id?

    What is your situation that you need to get information from incidents and technicians, yet you can't join these tables into the main query?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    Thanks Mark,
    I have both tables listed and joined in the query. There is an originator field which is stamped with a tech id number. Then there is also an owner which has it's corresponding tech id. For the owner field, I want to lookup the last name from the technicians table and stamp it into that field. Does that make sense?

    Thanks,
    Mark

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    Mark,

    You can add the technicians table to the query twice. The second one will get an alias technicians_1 or something like that.
    Join one of the technicians table to the main table on tech_id --- incident_ts_assignment
    Drop the last name field from this table to the grid and label it as owner (type Owner: in front of the field name in the grid)
    Join and the other technicians table to the main table on tech_id --- originator.
    Drop the last name field from this table to the grid and label it as originator (type Originator: in front of the field name in the grid)

    Note: if you allow incidents without originator or owner, you need to make the joins into outer joins, otherwise the query will only return those incidents for which originator and owner are not empty.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    Okay, that makes more sense. However, you don't need 2 DLookups. Since incidents is already a joined table, you only need a single DLookup. Assuming the tech_id is numeric, it probably will look like this:

    TSAssign: DLookUp("[tech_name_last]","technicians","[tech_id]= " & [incident_ts_assignments)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    Thanks again Mark,
    With this:

    TSAssign: DLookUp("[tech_name_last]","technicians","[tech_id]=" & [incident_ts_assignments])

    I get a missing operator error around "[tech_id]=" It doesn't say anything beyond that though. I even tried [incidents]![incident_ts_assignment] but that didn't work either. Any other ideas?

    Thanks,
    Mark

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Dlookup in Queries (A2K/Win2k)

    TSAssign: DLookUp("[tech_name_last]","technicians","[tech_id]=" & [incident_ts_assignments])


    Just to make sure, tech_id is actually a field in technicians, correct? And is it numeric or text?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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