User defined field

  • Thread starter Thread starter Kevin199
  • Start date Start date
K

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?
 
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
 
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
'====================================================
 
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?

Gina Whipp said:
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

Kevin199 said:
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?
Click to expand...
Click to expand...
 
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

Kevin199 said:
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?

Gina Whipp said:
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

Kevin199 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
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

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Back
Top