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
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdvsaProject financeAuthor Commented:
will be off computer for a while.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
pdvsaProject financeAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Then how do you expect your Dlookup() to work?  It would only return the first one it finds.

Jim.
0
pdvsaProject financeAuthor Commented:
Yes, and that is OK if it returns the first record.  Sorry for late reply.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well you should not be using a domain function in a query.  All the domain functions represent a SQL statement and are intended to be used in places where SQL statements are not allowed.   The query designer is not one of those places.

Dlookup() for example is nothing more then:

SELECT [<field>] FROM [<table>] WHERE <criteria>

Using them means poor query performance because the query parser cannot optimize the statement.

 So add the tables and join on the proper keys.  If that gives you more records then you want, you can use SELECT DISTINCT, or write a query that returns the description for the type, then use that as a "table" in the query you have now.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Ok.  I will drag the table in.  Does the same apply to Dcount?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.