Select query with changable field name

Question

Hi ,

I want to make a select query whose one field should be based on user requirement.

my table has fields :  ID, Name, fld1, fld 2, fld3....fld20.

Now a forms has a combo box having value fld1, fld2, fld3....fld20.

Now when a user select field name in combobox and hit "GO" i want to run a select query having fields ID, name and selected field by user, may be fld1, or fld2 or any.

I know it can be done but i don't know how.

Can any one help me to do that?

 Thanks

Answer 1

You can change the SQL of the query:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyQuery")
qdf.SQL = "SELECT [ID], [Name], [" & Me.ComboBoxName & "] FROM [MyTable]"

Change the names as needed.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Thanks Hans Vogelaar, I am going to check it and will intimate you

Answer 3

You might be interested in the following function which opens a datasheet of any SQL SELECT statement passed into it as a string expression:

Public Function OpenTempQuery(strSQL As String)

    Dim qdf As DAO.QueryDef
    Static n As Integer
    
    n = n + 1
               
    ' delete temporary querydef object if exists
    On Error Resume Next
    CurrentDb.QueryDefs.Delete "Temp" & n
    Select Case Err.Number
        Case 0
        ' no error
        Case 3265
        ' temporary querydef does not exist,ignore error
        Case Else
        ' unknown error
        MsgBox Err.Number, vbExclamation, "Error"
    End Select
    ' create temporary querydef object
    Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
    CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
    ' open query and then delete temporary querydef object
    DoCmd.OpenQuery "Temp" & n
    ' delete temporary querydef object
    CurrentDb.QueryDefs.Delete "Temp" & n

End Function

As many datasheets as you wish can remain open simultaneously by calling the function repeatedly, passing in a different SQL statement each time.  In fact, this is why I originally wrote the function, to provide a quick way of doing 'what if' comparisons by changing the WHERE clause of the SQL statement each time.

---

Ken Sheridan, Stafford, England

Answer 4

Thank you Hans Vegelaar,

It is exactly what I want.

Thank you

Answer 5

Thank you for Your Reply,

I am going to check it

Answer 6

Thanks Ken,

Its very nice and useful.