Dlookup query - Brining an #Error message

Question

I have an access database, and I'm trying to do a Dlookup on a query to pull information. Similar to a vlookup on excel.

Analyst Name: DLookUp("[Name]","Table2","[Analyst_ID]=" & [Analyst ID])

Analyst_ID and Name are located on Table 2 and give me the information I need. Analyst ID column is the raw data on table 1 and is the lookup value I wish to use.

The query runs but I get #Error

Please help!

You shouldn't use a DLookup in a query, simply add in and link the other table by the Analyst ID — Minty
Thank you for the comment, but how do I do this. I have added it as a relationship but im not sure how to continue. I want another column that's calls analyst name that will use the analyst ID from the table to check and compare in table 2 and give me the name associated with that ID — Hulk Smash 93
In the query designer, right click and select add table. Double click the second table. You should see it appear with the join already in place if it's been saved in the relationships. add the name field from the second table to your query. Bob is your Uncle. — Minty

Johnny Bones Johnny Bones · Accepted Answer · 2017-06-21T13:53:39

My guess is it's in the way Analyst ID is stored. This might work:

Analyst Name: DLookUp("[Name]","Table2","[Analyst_ID]='" & [Analyst ID] & "'")

However, with such a simple layout, you really should be doing it with an inner join query.

Create a new query. Add both tables to the query in design mode. Click and drag Analyst_ID from Table 1 onto Table 2.

You should now see a straight line between the tables, each pointing to Analyst_ID.

Drag-and-drop Analyst_ID and Name from Table 2 down into the grid below. Save that query as qryAnalyst.

Now, you can just write a simple SQL statement;

"SELECT * FROM qryAnalyst WHERE Analyst_ID =  " & [Analyst ID] & ""

Or, if Analyst_ID is stored as text, use:

"SELECT * FROM qryAnalyst WHERE Analyst_ID =  '" & [Analyst ID] & "'"

Dlookup query - Brining an #Error message

1 Answers