Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Sacramento, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select a field with a parameter (Access 2k)

    I am trying to create a query based on a table that will return the values only for the field named in the parameter. For example, if the table has the following fields: IDfield, Apples, Kumquats, Oranges; I want to be able to specify which field is returned. If I specify "Apples" I want IDfield and Apples for all records. Everything I've tried just puts the text "Apples" into the Apples field instead of returning the values.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select a field with a parameter (Access 2k)

    You need a calculated expression of the following form in a column of the query grid:

    MyField: Switch([Which Field]="Apples",[Apples],[Which Field]="KumQuats",[KumQuats],[Which Field]="Oranges",[Oranges])

    MyField will be the column heading of the field, and "Which Field" is the name of the parameter. If you want the column heading to be that of the selected field, I don't think that's possible in a stored query; you would have to create the SQL for the query on the fly in VBA code.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Sacramento, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select a field with a parameter (Access 2k)

    Dear Hans,

    OK, got it. It'll be a bit complicated since there are actually many more fields and some conditionals, but that's the start I need. Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select a field with a parameter (Access 2k)

    Yes, it's bound to get complicated. You're trying to make a query do something unusual. If things get hairy, it might be worthwile to create the query in code - seems complicated at first, but if you set it up systematically, it'll save probably time in the end.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select a field with a parameter (Access 2k)

    Hi Hans

    Do you have an example of creating quuuery in code?

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select a field with a parameter (Access 2k)

    John, the most important part is constructing the SQL. You can do this by concatenating fixed strings and variables. Then, you can use DAO (and probably ADOX) to create a new query, or modify the SQL of an existing query.

    Very simple example, to be called from a form with combo boxes displaying table names and field names:

    Dim strSQL As String
    Dim strField As String
    Dim strTable As String

    strTable = Me.cbxTableNames
    strField = Me.cbxFieldNames

    strSQL = "SELECT [" & strField & "] FROM [" & strTable & "]"

    CurrentDb.CreateQueryDef "qryNewQuery", strSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •