lookup, return value based on complicated criteria

[Dawnit]

How would you solve my problem?

Senario:
Table1 has 1 colunm of numbers (each greater than the previous by 6), field name is priceline:
10, 16, 22, 28, 34, 40, 46... through 298

Table2 has these fields:
materials (number)
markup (number)
priceline (lookup field)

Form1 has these fields/controls:
materials
markup
textbox1 =([materials]*[markup])
textbox2 - bound to priceline table2

Problem:
For each record, I want the value in textbox1 to be compared to all of the values in the priceline column of table1 and autofill textbox2 with one of the values from that field based on this:

if textbox1 is 12 or less, textbox2 should only fill with 10

if textbox1 is any number from 13 - 18, textbox2 should fill with 16

if textbox1 is any number from 19 - 25, textbox2 should fill with 22 ...and so on


I hope someone has a an answer, THANKS!!

 

[Rolliee]

send me your database (or a simple sample) along with the rules you mentioned above and I will code it using a recordsetclone of the underlying table and how to find the record you wish and set the values you want.

rollie@bwsys.net