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.

 

[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.