CODEWITHSUNDEEP
databasems-accessvba
user3553260
user3553260

Reputation: 721

DLOOKUP STRING CRITERA with simple example

I need help with retrieving the last names for the Authors on the Chapters table.

First, to start this example you need two tables:

tblAuthors

ID  FirstName   LastName
1   Rob         Cooper
2   Geoff       Griffith
3   Teresa      Hennig
4   Jerry       Dennison

tblChapters

ID  Author
1   Rob
2   Rob
3   Rob
4   Geoff
5   Geoff
6   Teresa
7   Jerry

while the tblChapters table is selected open, I click the create query button.

Then I add the fields ID and Author. To the right of Author I start a builder for my DLOOKUP().

My Dlookup uses strings.

It doesn't work.

Can you help me?

RetrieveLastName: DLookUp("[LastName]","[tblAuthors]","[FirstName]='" & [Author] & "'")

It works when I put for example:

RetrieveLastName: DLookUp("[LastName]","[tblAuthors]","[FirstName]='Teresa'")

but that is not what I want.

Thanks!

I have looked a multiple multiple websites and tried numbers syntaxes.

Upvotes: 1

Views: 1998

Answers (1)

ccarpenter32
ccarpenter32

Reputation: 1077

If I understand your question correctly, you're looking for data like the following (from the table tblChapters):

+----+--------+----------+
| id | Author | LastName |
+----+--------+----------+
|  1 | Rob    | Cooper   |
|  2 | Rob    | Cooper   |
|  3 | Rob    | Cooper   |
|  4 | Geoff  | Griffith |
|  5 | Geoff  | Griffith |
|  6 | Teresa | Hennig   |
|  7 | Jerry  | Dennison |
+----+--------+----------+

I would suggest not using a DLookup in a query setting, this will only cause you headaches.

I would suggest you use this SQL:

SELECT tc.id, tc.Author, ta.LastName 
FROM tblChapters tc
INNER JOIN tblAuthors ta ON tc.Author = ta.FirstName
ORDER BY tc.ID

If you are very inclined to use the Design View to setup a query in Access, use the following:

Query Design > Use Tables tblChapters and tblAuthors, link on tblChapters.Atuhor = tblAuthors.FirstName

+--------+-------------+-------------+------------+
| Field: |     id      |   Author    |  Lastname  |
+--------+-------------+-------------+------------+
| Table: | tblChapters | tblChapters | tblAuthors |
+--------+-------------+-------------+------------+

SIDE NOTE: This strategy only works in the case where there are no duplicated FirstNames and/or conflicts accross the two tables.

Upvotes: 1

Related Questions