dlookup just wont work

[jedrtd]

I need to use >= <= in a lookup, I'm not getting errors now but I get null. I have a weight1 and weight2 field which is the range fields, then on the form I have the weight, I need to check the weight1 and weight2 and get the cost for the weight from the table.

I'm using this in the control scr of my form not in VB Code

Thanks

 

[LonnieJohnson]

Is weight1 and weight2 in the table? If not, where do they come from? What is their relationship to &quot;the cost for the weight from the table&quot; that you metion above? ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[jedrtd]

weight1, weight2 and pmrate(this is the field witht he cost in it)are the fields in the table that i'm looking up

weight is the field you key into.

so you key the weight then lookup >= weight1 and <= weight2 move pmrate to cost field.

hope this helps

 

[LonnieJohnson]

OK. Sorry, I didn't catch on at first. With the assumption that your table is incremented in such a way...

w1 w2 cost
100 200 $50.00
201 300 $75.00
301 400 $100.00

The formula for the cost field on your form would be...

pmrate = DLookup(&quot;cost&quot;, &quot;MyTable&quot;, &quot;w1 <= &quot; & txtW & &quot; AND W2 >= &quot; & txtW)

On your form, I named the weight field txtW ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[jedrtd]

I tried this but alway got the smallest cost, shouldnt it be wt1 >= txtw and w2 <= txtw. I tried this now it is returning nothing again?

Thanks

 

[LonnieJohnson]

Is your table simular to the one above? Can you provide some a few lines emulating how the data is layed out. I tested the above example. I actually have it in the after update of the weight field.

Hence when I choose any weight amount between 100 and 200 then I get a cost of 50. If I choose an amount between 201 and 300 I get 75.

Again, I guess I'm not clear as to what it is your are actully trying to do. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV