DLookUp

[Jennifer]

I want to create a dlookup to look up a District Code

=DLookUp("[DistrictCode]","[tblDistrict]","[ManagerID] = " & [ManagerID])

tbldistrict has the DistrictCode and District ID

tblManagerDistrict has the ManagerID and District ID

The form has the Manager ID it needs to link to.

Can I set the criteria to 2 different tables?

 

[Chegu Tom]

I think you need to create a query that links tblDistrict and
tblManagerDistrict (joined on DistrictID?) and has the manager and
districtcode fields.

Do the Dlookup from the query

 

[Tom Wickerath]

Hi Jennifer,

Can I set the criteria to 2 different tables?

No. However, you can use a query instead of a table in the DLookup function.
The query could contain fields from more than one table. As an alternative,
since your form has the Manager ID, if the DLookup is used on the same form
you can pick up this value as a criteria. See "Refering to a form control" in
the article below:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I want to create a dlookup to look up a District Code

=DLookUp("[DistrictCode]","[tblDistrict]","[ManagerID] = " & [ManagerID])

tbldistrict has the DistrictCode and District ID

tblManagerDistrict has the ManagerID and District ID

The form has the Manager ID it needs to link to.

Can I set the criteria to 2 different tables?

 

[Jennifer]

Okay, got it set to a query and all is good but...

tblManagerDistrict will have the following records in some instances.

DistrictID ManagerID DistrictCode EndReason StartDate EndDate
2059 652 2111 Terminated 5/26/2009
2943 652 7515 7/27/2009
2943 652 7515 CORRECTION 7/27/2009 7/27/2009

I want display the CURRENT info in the form which would be the middle record
with the start date and no end date. Any suggestions?

Hi Jennifer,

Can I set the criteria to 2 different tables?

No. However, you can use a query instead of a table in the DLookup function.
The query could contain fields from more than one table. As an alternative,
since your form has the Manager ID, if the DLookup is used on the same form
you can pick up this value as a criteria. See "Refering to a form control" in
the article below:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I want to create a dlookup to look up a District Code

=DLookUp("[DistrictCode]","[tblDistrict]","[ManagerID] = " & [ManagerID])

tbldistrict has the DistrictCode and District ID

tblManagerDistrict has the ManagerID and District ID

The form has the Manager ID it needs to link to.

Can I set the criteria to 2 different tables?

 

[Tom Wickerath]

Hi Jennifer,

I'm not sure if your question is related to using the DLookup function, or
is this a new question regarding which rows to display in a form? If the
latter, I would think that a criteria of Is Null applied to the EndDate field
in a query should work. You would then use this query as the recordsource for
your form. Of course, that means that the other records will be filtered out
completely, although you could use VBA code to change the recordsource
(specifically the WHERE clause) if needed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________