User defined field

[Kevin199]

I would like to build a select query where the fields of a table are picked
from a form. I don't know how to select the field. It always ends up as
data. Please see my query: SELECT Size.ID, forms.YG.combo34 FROM [Size];
If combo34 = "weight", What do I need to do to make my query select the
Weight field from my table?

 

[Gina Whipp]

Kevin,

The query should be based on the table and then in the Criteria line of a
field in the query you can refrence the form...
=[Forms]![YourForm]![Combo34]. Also, I see you are using Size as a field
name. That is a reserved word and will most cause you problems. For a
complete list of Reserved Words see
http://allenbrowne.com/AppIssueBadWord.html#S

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John Spencer]

You would really need to build the query string using VBA.

If it was a matter of two fields, you could use something like the
following:

IIF(Forms!YG!Combo="Weight",[Weight],[Color])

Of course, that will give you either weight or color, but the column
name will be the same whether you return weight or color.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Kevin199]

Gina,
Thanks for the tip on nameing, I should know better. I still have my
problem with the query. Let me explain in more detail: I would like my
select query that is based on a table (80,153 lines and 42 fields) to return
80,153 lines and 2 fields. One field being an ID field and the other being a
field picked by the user on my form. In the end I would like to have the 2
column headings "ID" and the "user picked field" and the information from
only those fields in 80,153 lines. Can this be done from the query grid or do
I have to do somthing with table definitions?

Kevin,

The query should be based on the table and then in the Criteria line of a
field in the query you can refrence the form...
=[Forms]![YourForm]![Combo34]. Also, I see you are using Size as a field
name. That is a reserved word and will most cause you problems. For a
complete list of Reserved Words see
http://allenbrowne.com/AppIssueBadWord.html#S

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I would like to build a select query where the fields of a table are picked
from a form. I don't know how to select the field. It always ends up as
data. Please see my query: SELECT Size.ID, forms.YG.combo34 FROM [Size];
If combo34 = "weight", What do I need to do to make my query select the
Weight field from my table?

 

[Gina Whipp]

Kevin,

Okay, that changes things a bit. Not something I have ever done... The
only thing I can suggest is doing a search for dynamic queries.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
Thanks for the tip on nameing, I should know better. I still have my
problem with the query. Let me explain in more detail: I would like my
select query that is based on a table (80,153 lines and 42 fields) to
return
80,153 lines and 2 fields. One field being an ID field and the other
being a
field picked by the user on my form. In the end I would like to have the
2
column headings "ID" and the "user picked field" and the information from
only those fields in 80,153 lines. Can this be done from the query grid or
do
I have to do somthing with table definitions?

Kevin,

The query should be based on the table and then in the Criteria line of a
field in the query you can refrence the form...
=[Forms]![YourForm]![Combo34]. Also, I see you are using Size as a field
name. That is a reserved word and will most cause you problems. For a
complete list of Reserved Words see
http://allenbrowne.com/AppIssueBadWord.html#S

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I would like to build a select query where the fields of a table are
picked
from a form. I don't know how to select the field. It always ends up
as
data. Please see my query: SELECT Size.ID, forms.YG.combo34 FROM
[Size];
If combo34 = "weight", What do I need to do to make my query select
the
Weight field from my table?

 

[John W. Vinson]

Kevin,

Okay, that changes things a bit. Not something I have ever done... The
only thing I can suggest is doing a search for dynamic queries.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

You'll need to write VBA code to dynamically construct the SQL of the query.
Something like

Dim strSQL As String
strSQL = "SELECT ID, [" & Me!txtFieldname & "] FROM tablename;"

and then assign strSQL as the Recordsource of a form or report. A very much
less desirable choice would be to create a querydef based on strSQL and open
it as a query datasheet.

The need to do this makes me really queasy about the normalization of your
table. Users should never need to see or care about fieldnames.

 

[Gina Whipp]

John,

Thank you John... my new thing today...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Kevin,

Okay, that changes things a bit. Not something I have ever done... The
only thing I can suggest is doing a search for dynamic queries.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

You'll need to write VBA code to dynamically construct the SQL of the
query.
Something like

Dim strSQL As String
strSQL = "SELECT ID, [" & Me!txtFieldname & "] FROM tablename;"

and then assign strSQL as the Recordsource of a form or report. A very
much
less desirable choice would be to create a querydef based on strSQL and
open
it as a query datasheet.

The need to do this makes me really queasy about the normalization of your
table. Users should never need to see or care about fieldnames.