Dlookup 1

[hellohello1]

Can 3 tables be involed in a dlookup query?

My 3 tables are:

tblProject Status
Project ID (number) primary key
AS-of Date (date) primary key
ProjectName (text)
ProjectStartDate (date)
etc

tblKPI Rating
Project ID (number) primary key
AS-of Date (date) primary key
Overall KPI Score (number)

tblKPI Score
KPI Score (number)
KPI Color (text)

I have part of the dlookup that works and gives me the previous month's Overall KPI Score numeric value:

PreviousMonth: DLookUp("[overall KPI Score]","[tblKPI Rating]","[tblKPI Rating].[Project ID]= " & [tblproject status].[project id] & " And [as-of date]= # " & DateSerial(Year(Date()),Month(Date())-1,0) & "#")

This works, but instead of returning a number (Overall KPI Score), I would like to retun a text field by involving the tblKPI Score table, KPI Color text field.

My joins are:
INNER JOIN [tblKPI Rating] ON ([tblProject Status].[As-Of Date] = [tblKPI Rating].[As-of Date]) AND ([tblProject Status].[Project ID] = [tblKPI Rating].[Project ID])) INNER JOIN [tblKPI Score] ON [tblKPI Rating].[Overall KPI Score] = [tblKPI Score].[KPI Score];

So I would like to say: lookup the [KPI Color] field from the [tblKPI Score] table where [tblKPI Score].[KPI Score]=[tblKPI Ratings].[Overall KPI Score] AND [tblKPI Rating].[project id]=[tblProject Status].[project id] AND [tblKPI Rating].[as-of date]= last month.

Thanks!

 

[dhookom]

Can't you create a query to use as the domain in the DLookup() replacing the table name?

DLookup("[KPI Color]","[My New Query]","my criteria")

I typically try to avoid using DLookup() in a query since it slows the query.

Duane
Hook'D on Access
MS Access MVP

 

[hellohello1]

that worked! thanks.

I hadn't thought of that.

thanks again!