Dlookup for multiple criteria

[Edgarr8]

Hello,

I have a form which has two boxes that i want to use to go back to another table where i created a matrix with values.

My form has a box that asks for an Emmiter type[FormEmmiterID] and another one that asks for a Pressure[PSI Final]...

then one of my tables has the value when emmiter type[Emmiter] and pressure[Pressure] = ----- then my GPH (Gallons per hour) is? and i want that value to come back to my form...

this is what i have so far

=DLookUp("[GPH]","Emmiter Matrix",""[FormEmmiterID] =" & [Emmiter Matrix]![Emitter]&"" And "[PSI Final='"&[Emmiter Matrix]![Pressure]&"'")

Does anyone have a solution or a better idea how i can get that value?

 

[pbaldy]

Good reference on the syntax here, including a multiple criteria example at the bottom:

http://www.mvps.org/access/general/gen0018.htm

You have too many quotes.

 

[Edgarr8]

i changd it to
=DLookUp("[GPH]","Emmiter Matrix","[FormEmmiterID] = &[Emmiter Matrix]![Emitter]&" AND "[PSI Final= '"&[Emmiter Matrix]![Pressure]&"'")

Still not working

 

[pbaldy]

That doesn't follow the syntax from the link, but try:

=DLookUp("[GPH]","[Emmiter Matrix]","[FormEmmiterID] = " & [Emitter] &" AND [PSI Final]= '" & [Pressure] & "'")

 

[Edgarr8]

I got something with this formula

=DLookUp("[GPH]","Emmiter Matrix","[FormEmmiterID]= &[Emmiter Matrix]![Emitter]&" And "[PSI Final= & [Emmiter Matrix]![Pressure] &")

But sadly it only gives me the first value of GPH in my Emmiter matrix table..so its going to the correct table and bringing back the value from the column i want...but its not changing as my emmiterId and pressure change...any more ideas?

 

[pbaldy]

Yes; you still have extra quotes here, lack of quotes there, lack of brackets around the table name with the inadvisable space in it. It would help to know the data types of the 2 fields in the criteria.

 

[Edgarr8]

Thank you for everything Paul, what is the purpose of the &? Whoa....i started really analysing every little thing and i was way off here is what io have so far...
=DLookUp("[GPH]","Emmiter Matrix","[FormEmmiterID]= '" & [Emmiter Matrix]![Emitter] & "' " And "[PSI Final]= " & [Emmiter Matrix]![Pressure])
It comes back with a #Name?

 

[pbaldy]

You still have extra quotes in there, and it would still help to know the data types of the 2 fields in the criteria.

 

[Edgarr8]

Got it!!!!!
=DLookUp("[Diameter]","Emmiter Matrix ","[Emitter] ='" & [subform T_Rch Blk Details].[Form]![Emitter ID] & "'" & "And [Pressure]= [PSI Final]")

I had the Emmiter ID coming from a subquery which was connected by a ranch and block...
but the thing that was killing me was the & and " wow...that was something






Thanks Paul for pointing me in the right direction!