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