Multiple "where" criteria for a Dlookup?

[JJman]

Is there syntax to specify multiple "where" criteria in a Dlookup? I want it to display a value based on the combination of two other values in a table. Thanks!

 

[JeremyNYC]

"Field1 = 'asdf' and Field2 = 23" should do the trick.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JJman]

Ok, but how would I add the second field to the Dlookup below, since I have the first criteria concatenated? (I tried taking out the concatenation, but it didn't like it.)

=IIf(Not IsNull([rec_batch_id]),DLookUp("Comp_id","tbl_Completion_Record","Rec_Batch_id=" & [Rec_Batch_id]))

Thanks for your help!

 

[JJman]

I also tried this, but to no avail...

=IIf(Not IsNull([rec_batch_id]),DLookUp("Comp_id","tbl_Completion_Record","Rec_Batch_id=" & [Rec_Batch_id] And "employee_id=" & [employee]))

 

[JeremyNYC]

You're very close. The "And" has to be inside the quotes, because you want to pass it to Jet (the database engine) as part of the where clause, not as a separate parameter.

DLookUp("Comp_id","tbl_Completion_Record","Rec_Batch_id=" & [Rec_Batch_id] & " And employee_id=" & [employee])

should work produce a comp_id if the batch id isn't null. You're also missing the last parameter of the iif statement. The whole thing would look like this:
=IIf(Not IsNull([rec_batch_id]),DLookUp("Comp_id","tbl_Completion_Record","Rec_Batch_id=" & [Rec_Batch_id] & " And employee_id=" & [employee]), SomeOtherExpressionThatProducesAUsableValue)

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JJman]

Works great! Thanks again!