Dlookup to find value in a range

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

[pbaldy]

You've got the And outside the quotes, and didn't include the &. This has a multi-criteria example:

DLookup Usage Samples

Another option would be a non-equi join.