asked on

Dlookup, column(1)

Experts, The below Dlookup works but I need to tweek it to show the [AgtType] i/o the [TypeID]....(ie: its showing a number i/o the name). I think I need column(1) on [Type_Agt] but not sure how to do this. I put [Type_Agt].column(1) but that gives me an error.

i have this is in the query design grid of the report:
JV/Consort: DLookUp("[Type_Agt]","tblAgreements_thisPrj","ProjID= " & [Projects.ID])

[Type_Agt] has a row source of : SELECT tblAgreements_Type_DropBox.TypeID, tblAgreements_Type_DropBox.AgtType FROM tblAgreements_Type_DropBox ORDER BY tblAgreements_Type_DropBox.AgtType;

thank you

pdvsa

ASKER

will be off computer for a while.

Jim Dettman (EE MVE)

You should not be using a domain function inside a query.

Instead, add table tblAgreements_thisPrj and tblAgreements_Type_DropBox tables to your query and join them on the appropriate keys (ProjID and TypeID)

Then pull down the AgtType field from tblAgreements_Type_DropBox into the grid.

Jim.

pdvsa

ASKER

Oh ok I thought about that but there is a one to many relationship. I think I will have duplicates unless I can limit them somehow?

Typing from phone

Jim Dettman (EE MVE)

Then how do you expect your Dlookup() to work? It would only return the first one it finds.

Jim.

pdvsa

ASKER

Yes, and that is OK if it returns the first record. Sorry for late reply.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

Ok. I will drag the table in. Does the same apply to Dcount?

Jim Dettman (EE MVE)

Yes, DCount() as well. Any of the Domain Functions (ie. DSum(), DMin(), DMax(), etc). All represent SQL statements.

When your working in a queries, all those statements can be written directly in one form or another.

The domain functions where intended to be used in places where SQL statements cannot. For example, as the controlsource of a control.

Jim.