Results 1 to 6 of 6
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    DLookup won't work

    These lookup function always give me fits, when to use brackets, when to use quotes, when to use both and where. Well, I've been off the job for a month or more which means I've forgotten how to do basic things like a dlookup function. I've set up a very simple one that OUGHT to work. I've tried a dozen variations and I can't retrieve what I need. Here's as basic version and the result, just a blank field where the function is.

    Code:
    SELECT Applicant.OrgNum, DLookUp("[Organization_Name]","Organization","[Application!OrgNum]"="[Organization!OrgNum]") AS OrgName
    FROM Applicant;
    OrgNum OrgName
    001
    003
    004
    009
    010
    011
    013
    014
    015
    017
    020


    OrgNum is the Primary Key to Organization and a Foreign Key in Applicant. If I link the tables in the query, it works fine, but for my own purposes. At least I want to test it out, but I really want to know what I am doing wrong. Here is the linked table version and the results. You can see the dlookup returns nothing.

    Code:
    SELECT DISTINCT Applicant.OrgNum, Organization.Organization_Name
    FROM Applicant INNER JOIN Organization ON Applicant.OrgNum = Organization.OrgNum;
    OrgNum Organization_Name
    001 New Beginnings
    003 Rappahannock General Hospital
    004 Mary Washington Hospital, Inc.
    009 Chessen & Associates, PC
    010 Carpe Diem of Virginia, Inc.
    011 Avery Finney Psychotherapy Associates, PLC


    013 Sellati & Co., d/b/a Woodbridge Methadone Treatment Center
    014 Strategic Therapy Associates, Inc.
    015 Family Teamwork, Inc.
    017 VersAbility Resources
    020 PHC of Virginia, Inc.

    The DISTINCT is optional. I just used it to create of more concise table. So the question is, Why won't the function work?

    Thanks in advance

    Paul

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    DO NOT use Dlookup in a query.
    The query IS the lookup. Join your field to the lookup table to get the result.

    like join your data table to the tOrg table, joined on ID field, the result will be the name.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Well, I feel like I've used the dlookup hundreds of times this way, but linking tables is far simpler. I am using the query as source for a combo box which runs very slow. I thought the lookup might speed things up.

    Thanks,

    Paul

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    On the contrary - DLookUp is just about THE slowest way to get information. There must be some other reason why your query is running slowly. Network? Table key structure?

  5. #5
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    I think it might be the Window's file server which is shared by many users. I indexed the field in the combo box and that helped, but we've been through this before, excellent performance followed by period slow downs. I think periodic compacting of the back end may also be necessary. Also considering upsizings to SQL Server.

    Thanks for your suggestions.

    Paul

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Be very sparing with compacting the back-end - and ALWAYS make a copy before compacting!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cannot get DLookUp to work with concatenated criteria
    By sportyaccordy in forum Queries
    Replies: 7
    Last Post: 07-21-2016, 09:12 AM
  2. Can't get my Dlookup to work!
    By Maxelkat in forum Forms
    Replies: 3
    Last Post: 05-19-2011, 06:00 AM
  3. Would DLookup() work better here?
    By eww in forum Programming
    Replies: 2
    Last Post: 09-30-2010, 01:53 PM
  4. Replies: 2
    Last Post: 11-04-2009, 09:45 PM
  5. Replies: 1
    Last Post: 01-10-2006, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums