Using Dlookup to pull value from table with multiple criteria

[jobrien4]

I am trying to use the Dlookup function but am running into some issues when I try to use multiple criteria.

I have a table that has the a price for a given range of thicknesses. I want the Dlookup to pull the price from the correct range. The ranges are defined by two fields, LowThickness and HighThickness.

For example, let's say I have a different price if the thickness is 0 to 1, 1 - 2, and anything over 2. I want the user to be able to input a number, say 1.556, and have it lookup the correct cost from the 1-2 range.

Here is what I have:

MaterialCost = DLookup ( "[Cost]","[tbl_MaterialCost", "[LowThickness]< " & Thickness And "[HighThickness] >= " & Thickness & "")

What am I doing wrong?

Edit: If there is a better or simpler way to accomplish this, including redesiging the tables involved, I'm all ears.

 

[bwellbor]

You've got an extra bracket that shouldn't be there. The "And" needs to be in the quotes. And you probably don't need the & "" at the end. This is what you have:

MaterialCost = DLookup ( "[Cost]","[tbl_MaterialCost", "[LowThickness]< " & Thickness And "[HighThickness] >= " & Thickness & "")

And this is probably what you need:

MaterialCost = DLookup("[Cost]", "tbl_MaterialCost", "[LowThickness]<" & Thickness & " And [HighThickness]>=" & Thickness)