Queries with field names defined by a control in a form

[Gwin]

I want to create a select query where I can define which field is used
by a control in a form.

For instance if I have a table tServices, in which there are fields
ServiceDate, ServiceType, ServiceCategory, ServiceName

Rather than creating three separate queries to view ServiceDate
combined with one of the other fields, I would like to define the
field by selecting it from a form, for instance:

Select ServiceDate, [Forms]![Form1]![txtFieldName] as
SelectedFieldName
FROM tServices

The above query doesn't do what I want, it just creates a field where
every record has the selected field name, rather than the data stored
in that field. How do I write the expression properly?

I have created ways to work around this, using modules with
docmd.runsql method, because then I can define the sql as a text
string and insert variables into it as certain points. However, this
isn't really what I want to do.... I just want a select query, not an
action query.

Thanks to all who have given advice in previous queries!

gwin

[Bob Quintal]

Gwin <do*********@verizon.net> wrote in
news:s8********************************@4ax.com:

I want to create a select query where I can define which field
is used by a control in a form.

For instance if I have a table tServices, in which there are
fields ServiceDate, ServiceType, ServiceCategory, ServiceName

Rather than creating three separate queries to view
ServiceDate combined with one of the other fields, I would
like to define the field by selecting it from a form, for
instance:

Select ServiceDate, [Forms]![Form1]![txtFieldName] as
SelectedFieldName
FROM tServices

The above query doesn't do what I want, it just creates a
field where every record has the selected field name, rather
than the data stored in that field. How do I write the
expression properly?

I have created ways to work around this, using modules with
docmd.runsql method, because then I can define the sql as a
text string and insert variables into it as certain points.
However, this isn't really what I want to do.... I just want
a select query, not an action query.

Thanks to all who have given advice in previous queries!

gwin

Instead of using the runsql method, change the code to
createquerydef, openquery and then delete the querydef
--
Bob Quintal

PA is y I've altered my email address.

[Pieter Linden]

You'll have to build the SQL statement yourself in a string variable
and then assign it to a query's SQL property and save the query.

[Allen Browne]

Where is this headed? Can you assign the string to the RecordSource of the
form or report? Or even to the SQL property of the QueryDef?

strSql = "SELECT ServiceDate, " & Forms!Forms1!txtFieldName & " AS
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gwin" <do*********@verizon.net> wrote in message
news:s8********************************@4ax.com...

I want to create a select query where I can define which field is used
by a control in a form.

For instance if I have a table tServices, in which there are fields
ServiceDate, ServiceType, ServiceCategory, ServiceName

Rather than creating three separate queries to view ServiceDate
combined with one of the other fields, I would like to define the
field by selecting it from a form, for instance:

Select ServiceDate, [Forms]![Form1]![txtFieldName] as
SelectedFieldName
FROM tServices

The above query doesn't do what I want, it just creates a field where
every record has the selected field name, rather than the data stored
in that field. How do I write the expression properly?

I have created ways to work around this, using modules with
docmd.runsql method, because then I can define the sql as a text
string and insert variables into it as certain points. However, this
isn't really what I want to do.... I just want a select query, not an
action query.

Thanks to all who have given advice in previous queries!

gwin