Dual Criteria for DLookup

[TB11]

Hi.

I'm trying to do a dual criteria, but I either get errors or the wrong person for the DLookup.

This part works fine: Expr1: DLookUp("[PersonFullName]","Person","[IDPerson]=" & [fkPerson])

When I try to add in the second criteria, that the fkRole =2 is where I'm stuck. (The fkRole is already part of the query.) The fields in the query are: fkPerson, fkRole, Expr1,
Exp2 for the results that I am struggling with.

Any thoughts?

Thanks.

 

[theDBguy]

Have you tried something like?

DLookUp("[PersonFullName]","Person","fkRole=2 AND [IDPerson]=" & [fkPerson])

 

[isladogs]

TheDBGuy was faster on the draw...
As above ... but for info you don't need the square brackets as your field names have no spaces or special characters

DLookUp("PersonFullName","Person","fkRole=2 AND IDPerson=" & fkPerson)

 

[TB11]

@theDBguy and @isladogs I copied both of your solutions to the query and I received no results each time.

 

[Pat Hartman]

Then, no records satisfy BOTH criteria. Create a query that uses the same criteria. Do you get the same results?

 

[plog]

Expr1: DLookUp("[PersonFullName]","Person","[IDPerson]=" & [fkPerson])

DLookups have no place in a query.

When you are building a query and need data from a source (query or table) you bring that source into the query and link appropriately. If you need to apply criteria to it you do so inside the query as well.

 

[TB11]

@Pat Hartman You rock. You are right. No records satisfied both criteria. You got me thinking on the right track. This is how I got it to work.

Expr1: IIf([fkPerson]=2, DLookUp("[PersonFullName]","Person","[IDPerson]=" & [fkPerson]))

THANK YOU!

 

[theDBguy]

@Pat Hartman You rock. You are right. No records satisfied both criteria. You got me thinking on the right track. This is how I got it to work.

Expr1: IIf([fkPerson]=2, DLookUp("[PersonFullName]","Person","[IDPerson]=" & [fkPerson]))

THANK YOU!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

This is how I got it to work.

plog is correct, domain functions do not belong in queries. They can almost always be replaced by outer joins to the lookup table for significantly more efficiency. You can still use the IIf() if you only want to show the person name for certain roles.

Did you really mean?

Expr1: IIf([fkPerson]=2, DLookUp("[PersonFullName]","Person","[IDPerson]=" & [fkPerson]))

That is looking up the name only for a specific Person. That doesn't seem rational.