Selecting field names instead of field values

[CuriousGeorge]

Hello,

My query doesnt quite work as i want to;

on my form i have three combo boxes and i want my query to select the values and ranges that are selected from these three boxes.

SELECT [Forms]![frm]![ComboParameter].value AS Expr1
FROM TableData
WHERE (((ReturnDate([AbsTime]))=[Forms]![frm]![ComboDate]) AND ((TableData.BananaField)=[Forms]![frm]![ComboType]));

In the ComboParameter box i have all the field names listed which are in my table 'TableData'.

My problem is;

if one field name is selected i want all the values within that field to be selected and filtered according to the WHERE clause. As it is now the field name is listed x times instead of the values within that field.

Any ideas? (i added .value to the expression but didnt work).

Thanks very much

 

[Jeanette Cunningham]

There is a great little search sample database that does what you want with 3 or more combos here:
http://www.allenbrowne.com/ser-62.html

 

[JANR]

You could use DAO.Query to modify a query:

Dim qdf As DAO.QueryDef
Dim s As String
s = "SELECT " & Me.ComboParameter & " FROM TableData " & _
    " WHERE (((ReturnDate([absTime])) = Forms!frm!ComboDate) AND ((TableData.BananaField)= " & _
    " Forms!frm!ComboType));"
Set qdf = CurrentDb.QueryDefs("qryAll")
qdf.SQL = s
DoCmd.OpenQuery ("qryAll")

JR

 

[DCrake]

This won't work form previous post

DoCmd.OpenQuery ("qryAll")

Cannot use openquery on a select type query must be an action query.

 

[CuriousGeorge]

thank u all

but it is not really helping me.

The query works on two of the combo boxes. But the third combo box which is suppose to select one of the fields (parameter) doesnt work.

It is a combo box which contains all the field names in the table. And my idea is that one of the fields should be selected when selected in the combo box (ComboParameter).

How do i do that? Is it possible to do in the query wizard setup?

For example even though im selecting all the fields in the query. How do i set the criteria in the WHERE clause?

i.e. WHERE ((ReturnDate([AbsTime]))=[Forms]![frm]![ComboDate]) AND ((TableData.BananaField)=[Forms]![frm]![ComboType]) AND [Forms]![frm]![ComboParameter]=?????)

I guess an option here is to add several OR statements, but since i have 14 fields (parameters) the code gets kinda messy if doing so. There has to be a smarter way of doing it?

thanks

 

[CuriousGeorge]

When i use this query it is filtering correctly from ComboDate AND ComboType, but it is displaying the selected ComboParameter as a name(the field header) rather than all the values that is contained within that field:

SELECT ([Forms]![frm]![ComboParameter]) AS Expr1
FROM TableData
WHERE (((ReturnDate([AbsTime]))=[Forms]![frm]![ComboDate]) AND ((TableData.BananaField)=[Forms]![frm]![ComboType]))

 

[JANR]

This won't work form previous post

DoCmd.OpenQuery ("qryAll")
Cannot use openquery on a select type query must be an action query.

Yes, if you look at the code I am modifying a stored query with a new SQL!

but it is displaying the selected ComboParameter as a name(the field header) rather than all the values that is contained within that field:

The code I gave assume that you have a combobox set to a Valuelist which looks like this:

RowSource: ID;Name;Adress etc.

If I understood you correctly, you want to dynamically change the "field" after your SELECT statement?

Then the code does just that.

s= "SELECT " & Me.ComboParameter & " ....

Inserts the tablefield name chosen in you combobox so you get the correct SQL syntax.

"Select MyField FROM TableData....."

JR

 

[CuriousGeorge]

No i dont want the table field name to be inserted. I want the values that is contained within the field to be listed in the query when the corresponding name is selected in the combo box.

Example:

ComboParameter: Voltage
ComboDate: 2010-01-01
ComboType:000000-D00

i.e the query shows the values that is contained in the voltage field and filtered on the date and the type.

The date and type works but it is listing the field name 'voltage' on each filtered record instead of the value for each record under voltage.

Sorry if not being clear.

thanks

 

[JANR]

Basic SQL: http://beginner-sql-tutorial.com/sql-select-statement.htm

SELECT Fieldname
FROM TableName
WHERE Something;

So you cannot use:

SELECT '12v' As Voltage
FROM TableName
WHERE Something;

What is JET suppose to find and where in the table is it suppose to look?

What is the Rowsource of ComboParameter?

JR

 

[CuriousGeorge]

I know, thats why it is so strange. Has to be some setting that is wrong.

my sql statement is as follows:

SELECT ([Forms]![frm]![ComboParameter]) AS Export
FROM TableData
WHERE (((ReturnDate([AbsTime]))=[Forms]![frm]![ComboDate]) AND ((TableData.BananaField)=[Forms]![frm]![ComboType]))
ORDER BY TableData.AbsTime;

My row source type is set to value list and in row source ive put each field name. I have also tried using the field list as an option but it generates the same thing.

If my table looks like this:

Voltage Current Temperature SomeDate FileType
2 -1 10 2010-01-01 0001-D00
4 0.02 12 2010-02-02 0002-D00
5 0.08 15 2010-02-02 0002-D00
7 0.05 13 2011-03-09 0001-D00

If my combo boxes are selected as follows:

ComboParameter: Voltage
ComboDate: 2010-02-02
ComboType: 0002-D00

I want the query to list the number 4 and 5.

As it is now it only shows "Voltage" "Voltage".

 

[JANR]

To illustrate see attached db

JR

 

[CuriousGeorge]

Yeah that works really nicely.

I still dont understand though why it is not working without using vba and only using queries and wizard.

Anyway thanks

 

[Galaxiom]

This won't work form previous post

DoCmd.OpenQuery ("qryAll")

Cannot use openquery on a select type query must be an action query.

This is incorrect. No doubt David is thinking of DoCmd.RunSQL which only works for action queries.