Hi,
Im new to access and trying to link the data (rows and columns) of my excel table to a text box. I have created an excel file table in access that contains the info I need to use. However, I cannot get to the data by row and column. Below is a sample of my excel table. Im trying to pull the data when the criteria is true. As an example, if Qty_ID =2 (73-143) And there are 3 colors, I need to pull the 1.95 from the expression. Or does this need to VBA coded? I cant seem to write the correct expression to retrieve the data by rows and columns. 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

Phil,

Your problem statement is VERY confusing.

It sounds like, first of all, you are starting out with an Excel spreadsheet.
Then, it sounds like you've imported/linked that spreadsheet into an Access database.

If my understanding above is correct, then let's completely forget about Excel going forward. In other words, once you've gotten this data into Access, for all intents a purposes, you are dealing only with an Access table--the fact that it came from Excel is completely immaterial.

So, you have an Access table with data in the structure you have described.

You mention a text box, but you don't mention what the function of this text box is. Is someone entering the Qty_ID in this text box and you expect the application to return some value? It sounds like you also need to have some sort of entry control specifying the number of colors. In other words, it sound like you are expect the system to return the intersection of a specific Qty_ID row and one of the color columns, but you haven't told us how we are getting either of those two values.

You really need to do a better job of describing your needs.

Hi Ken,

Thanks for your response! I apologize for my inability to clarify what I'm trying to do and what I need... Let me try once again.. I appreciate your patience.

The (Excel) access table I referred to is a table that gets updated once at the beginning of the year. The information contained therein are the values Im trying to pull into the text box.

The expression I used (that does not work) goes like this "=IIf([color_box]=1 And [Text580]=1,[Imp Qty 2]![Qty1_ID]=2,[column](3))
Note: [color_box] is the number of colors. [text580] are the quantities 36-72, 73-143 etc. and [imp Qty 2] is the table and im trying to pull the value in column 3. I hope thats clear enough to understand.


As you have correctly described, I need this text box to return the value of a specific intersection of a Qty_ID row and one of the color columns. What I don't know is whether I need to use an expression or script to return the specific value from that intersection as described above.

Please let me know if this is clear. Im trying my best here. Thanks!!