Re: Parameter as field name

Daron, I don't believe you can do that, but it is very easy to create the
SQL statement in VBA on the fly.

For example, if you a combo box with these properties:
Row Source Type Field List
Row Source Table1
Name cboField
you could write this code:
Dim strSql as String
If Not IsNull(Me.cboField) Then
strSql = "SELECT [" Me.cboField & "] FROM Table1;"
End If

You can then assign this to an existing query:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql
or a form:
Me.RecordSource = strSql
or to a report (in its Open event.)

It would be possible to use a multi-select list box to select multiple
fields, and then loop through its ItemsSelected collection to build up the
query statement. (These techniques avoid the user choosing invalid field
names.)

Ultimately, though, the disappearing fields will cause problems for any form
or report that has controls bound to those fields, so the question may hint
that the data structure is not relational (e.g. the fields are repeating
names where they should be records in a related table.)

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

"Daron" <Daron.Lowell@xxxxxxxxx> wrote in message
news:1145387123.409618.94520@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Is it possible to use a parameter as the field name, not just a
criteria?

Snippet of my SQL:

SELECT Eval("[Forms]![frm__Setup]![lbo_CurrentFields].[Column](0)") AS
TestField, [TestField] AS [Current Value] from qry_main

The parameter [TestField] will hold the name of the field. In the first
column, I want to show the name of the field for all records. In the
second column, I want to show the actual value of this field for the
current record:

STR_STUDENTID 999999
STR_STUDENTID 999982
STR_STUDENTID 999945

I hope this makes sense. I know that this is probably so simple I'm
looking right at it, but I can't seem to figure this out.

TIA



.

Relevant Pages

  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL statment errors
    ... If you believe there is an error in your SQL statement, ... Dim myFieldName As String ... Dim strSQL As String ... "Click YES use your new value and overwrite the previous value." ...
    (microsoft.public.access.modulesdaovba)
  • Re: Why SQL command doesnt work
    ... Simply creating a SQL statement doesn't do anything by itself: ... DoCmd.RunSQL Strsql ... Note, though, that it requires a reference to be set to DAO. ... > Private Sub Command6_Click ...
    (microsoft.public.access.gettingstarted)