Form text box

[pmlazalde]

Hi,



Im new to access. Using a form, Im trying to pull data from a specific intersection of rows and columns on my table (see sample below) to a text box. I have created a table in access that contains the info I need to use. This table gets populated once at the beginning of each year and does not get changed until the new year. However, I cannot seem to pull the data at a specific intersection of a row or column using an expression.. Below is a sample of my table. I'm trying to pull the data when the criteria is true. As an example, if Qty_ID =2 and desc is between 73-143, and there are 3 colors, I need to pull the 1.95 into the text box form. Does this need to be a VBA script or can I use an expression?? Please help!! Im losing hope! Thanks in advance for any help you can give.. I appreciate it! Phil



Qty_ID desc 1 color 2 color 3 color 4 color 5 color

1 36-72 1.55 2.10 2.65 3.20 3.75
2 73 -143 1.05 1.50 1.95 2.40 2.85
3 144-499 0.80 1.10 1.40 1.70 2.00
4 500-800 0.45 0.65 0.85 1.05 1.25
5 801-1100 0.40 0.55 0.70 0.85 1.00



Please let me know if this is clear. I'm trying my best to explain it properly. Any help will be much appreciated, Thanks in advance for your time!

Phil

[June7]

Data is not normalized which makes this harder. The desc is not numeric, it is a string. If user input is 69 (a number), it has no way to determine that 69 falls in the 36-72 range because that data is a text value.

Will user desc input be a number or will they chose the range from a combobox list?

How do you know there are 3 colors? Colors of what?

[pmlazalde]

Thanks for answering. Yes, user will choose from a combo box list. The colors come from another combo box list I've created. We are a print shop and the colors mandate how much we charge, i.e, 2 colors are .40 cents, 3 colors .50 cents etc.. I'm trying to use an expression that says if desc (box) is between 36 and 72 (Quantities) , and color equals 2 colors, then return value from that intersection. I just don't know how to return a value from a specific intersection. Hopefully, I explained this right and haven't confused you. Sorry if i have. Thanks

Phil