DLookup on two criteria

[Coral]

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:

I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in


DLookUp("[ValidDept]","T530_ANZ","ValidDept =

.[Data].
[Department]")

but i end up getting error: '....Access can't find the name

.
[Data].[Department]'

 

[pietlinden]

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:

I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in

DLookUp("[ValidDept]","T530_ANZ","ValidDept =

.[Data].
[Department]")

but i end up getting error: '....Access can't find the name

.
[Data].[Department]'

why not just use a query? IF you need to have two fields in your
join, then that's no problem...

 

[Coral]

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:

I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in

DLookUp("[ValidDept]","T530_ANZ","ValidDept =

.[Data].
[Department]")

but i end up getting error: '....Access can't find the name

.
[Data].[Department]'

why not just use a query? IF you need to have two fields in your
join, then that's no problem...- Hide quoted text -

- Show quoted text -

Hi..the problem in using two joins is that my table fields (Dept) is
not unique...so for bothe countries Aust and NZ we have same dept that
get repeated...so in Data table (50,000) dept (many repeated) and T530
table (dept are repeated) and so joining brings multiple records

 

[John W. Vinson]

Hi..the problem in using two joins is that my table fields (Dept) is
not unique...so for bothe countries Aust and NZ we have same dept that
get repeated...so in Data table (50,000) dept (many repeated) and T530
table (dept are repeated) and so joining brings multiple records

So join as Piet suggests - on BOTH fields. There would be two join lines in
the query - dept joined to dept, country to country. You're not limited to
joining on just one field!

John W. Vinson [MVP]