passing variables from a form to a query 2

[Webkins]

I have an Access 2003 form named frm_oven, with a combo box named oven. The value list of the oven combo box is a set to a number from 1 thru 8. The data is in table tbl_oven I want to retrieve. The table column name are structured like this: oven_4_p1b, oven_4_p2b, oven_4_p3b and oven_4_p4b

I need to pass the selected number (1 thru 8) to an Access query named qry_oven as shown below. I have tried this query with no luck.

SELECT tbl_oven.Tiempo, tbl_oven.oven_[forms]![frm_oven]![oven]_p1b, tbl_oven.oven_[forms]![frm_oven]![oven]_p2b,
tbl_oven.oven_[forms]![frm_oven]![oven]_p3b, tbl_oven.oven_[forms]![frm_oven]![oven]_p41b, tbl_oven.Fecha,
tbl_oven.LDO, tbl_oven.LDC, tbl_oven.RDO, tbl_oven.RDC
FROM tbl_oven
ORDER BY tbl_oven.Fecha, tbl_oven.Tiempo;

I have been looking on how to correctly code this with no luck. I am thinking maybe I need to use a "tag" but have never done this and have no idea how it works. Any advice, examples, suggestions. Thank you all so much for your help.

 

[dhookom]

You might want to read up a bit on normalization. That said, try:

SELECT tbl_oven.Tiempo, 
Choose([forms]![frm_oven]![oven],oven_1_p1b,oven_2_p1b,oven_3_p1b,...etc) as p1b,
Choose([forms]![frm_oven]![oven],oven_1_p2b,oven_2_p2b,oven_3_p2b,...etc) as p2b,
...etc..., 
tbl_oven.Fecha,tbl_oven.LDO, tbl_oven.LDC, tbl_oven.RDO, tbl_oven.RDC
FROM tbl_oven
ORDER BY tbl_oven.Fecha, tbl_oven.Tiempo;

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Perhaps...

SELECT Tiempo, oven, p1b, p2b, p3b, p4b, Fecha, LDO, LDC, RDO, RDC 
FROM tbl_oven 
WHERE oven = Forms!frm_oven!oven 
ORDER BY Fecha, Tiempo

Randy