please help to build SQL

[mpb.vu2]

hello friends... back in business after a long time...

I need some help to make a simple (?) query.

I have my table as follows:

monthid month year riceprice wheatprice maizeprice
1 jan 2007 350 450 340
2 feb 2007 355 455 345

and so on...

i have a form with a list box named commodity containing the list of these commodities. i.e. Rice, Wheat and Maize.

i want to write an SQL statement in the preview button of the form in such a way that when i select riceprice from the listbox of the same form, and click preview, then a simple query will show the fields monthid, month, year and riceprice... when select wheat then only wheat price will appear... and so on.

I know i can do it simply making 3 different queries, but i want to write SQL in the form instead.

Please help me.

 

[KeithG]

You can use the value from the listbox as criteria in your query. Use the below (filling in the names of your objects) in the price criteria

[Forms]![FormName]![ListBoxName]

 

[supercool]

a listbox would be heplful, actually the preview button might not be necessary.
Guess you can adjust the "column count" and "Column width" property of listbox to get the effect u want.

Another way is to :
store the listbox selection value into a variable , lets say it lstItem
in ur SQL criterial, you could use
stSQl = "SELECT monthid, month, year, " & lstItem & " FROM tbl.."

so everytime, when user makes different selection, the result of running SQL statement would return different queried records.
Hope that is what you want.

 

[mpb.vu2]

thanx for the reply...but i want something different...

riceprice wheatprice maizeprice are different columns...
i need to show the specific COLUMN....

 

[mpb.vu2]

thanx supercool

i what like that...
pls. tell me where i should store the values... in a separate table?

 

[supercool]

it's ok. I would suggest you declare one variable with string data type under the Onchange event of the listbox, and then proceed with the SQL statement.
anyway, all those codes would be preferably implemented in VBA

 

[mpb.vu2]

well... i am novice here..

i uploaded my db.

pls. help me writing the query

i want to select the comodity and click the button to view the specific column.

thanx

 

[supercool]

here it is, this function seems easy, but still require a bit of thinking to finish it, anyway, enjoy it