Add Fields in a Query on Fly (1 Viewer)

[JithuAccess]

Hello Guys,

May be a very stupid question, But is there any way we can add Fields in a Query on fly?

There are 5 Fields in my Query and in a Form all the Fields I am showing in a Check Box when a user select 3 Fields from the Check Box and click on Run Query Command Button, I want to just show these 3 Fields.

Is it possible?

Thanks,

This is my Table Fields

This is my Form

 

[plog]

So you're building the functionality of Microsoft Access with Microsoft Access? Why not just teach users how to use the Query Designer?

2 more points before some help: 1. [Emp Salary] really sounds numeric to me--why is it a Short Text field? 2. You shouldn't use spaces in field names--makes coding/querying that much more difficult.

My advice to accomplish this would be to build a Report on your table, Then you can programmatically hide columns of the report based on what the user wants to see. Additionally, you can programmatically sort the report and filter it--which users always request after you give them the functionality you are working on. But then again, filtering and sorting is solved by teaching them how to use the Query Designer.

 

[arnelgp]

you build the query string:

    Const QUERY_NAME As String = "zzQuery"
    Dim sSQL As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    sSQL = "SELECT "
    Set db = CurrentDb
    If [chkEmpID] Then
        sSQL = sSQL & "[Emp ID],"
    End If
    If [chkEmpName] Then
       sSQL = sSQL & "[Emp Name],"
    End If
    If [chkEmpAddress] Then
        sSQL = sSQL & "[Emp Address],"
    End If
    If [chkDesignation] Then
        sSQL = sSQL & "[Emp Designation],"
    End If
    If [chkSalary] Then
        sSQL = sSQL & "[Emp Salary],"
    End If
    If sSQL <> "SELECT " Then
        sSQL = Left$(sSQL, Len(sSQL) - 1) & " From yourTableName;"
        On Error Resume Next
        Set db = CurrentDb
        DoCmd.DeleteObject acQuery, QUERY_NAME
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
        Set qd = db.CreateQueryDef(QUERY_NAME, sSQL)
        db.QueryDefs.Append qd
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
        Set qd = Nothing
        Set db = Nothing
        DoCmd.OpenQuery QUERY_NAME
    End If

 

[JithuAccess]

you build the query string:

    Const QUERY_NAME As String = "zzQuery"
    Dim sSQL As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    sSQL = "SELECT "
    Set db = CurrentDb
    If [chkEmpID] Then
        sSQL = sSQL & "[Emp ID],"
    End If
    If [chkEmpName] Then
       sSQL = sSQL & "[Emp Name],"
    End If
    If [chkEmpAddress] Then
        sSQL = sSQL & "[Emp Address],"
    End If
    If [chkDesignation] Then
        sSQL = sSQL & "[Emp Designation],"
    End If
    If [chkSalary] Then
        sSQL = sSQL & "[Emp Salary],"
    End If
    If sSQL <> "SELECT " Then
        sSQL = Left$(sSQL, Len(sSQL) - 1) & " From yourTableName;"
        On Error Resume Next
        Set db = CurrentDb
        DoCmd.DeleteObject acQuery, QUERY_NAME
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
        Set qd = db.CreateQueryDef(QUERY_NAME, sSQL)
        db.QueryDefs.Append qd
        db.QueryDefs.Refresh
        Application.RefreshDatabaseWindow
        Set qd = Nothing
        Set db = Nothing
        DoCmd.OpenQuery QUERY_NAME
    End If

Thanks a lot. Let me test this

 

[Uncle Gizmo]

My export to excel tool might be of interest:-

Show-Hide-Export to Excel – Nifty Access

www.niftyaccess.com

If you would like a free copy, PM me private message me, for details on how to get a free copy...

 

[arnelgp]

you need to replace My checkbox names with the name of your checkbox.
also replace the fieldnames and table name (yourTableName).

 

[JithuAccess]

you need to replace My checkbox names with the name of your checkbox.
also replace the fieldnames and table name (yourTableName).

Sure

 

[Pat Hartman]

Thanks a lot. Let me test this

Just because you can do something, doesn't mean that you should. Users should NEVER, EVER be given live queries to view. Unless you make the query not updateable by adding an aggreagte field, the user will be able to use that query to add/change/delete fields. Seriously poor practice. Users should interact with forms and reports where you can easily control not only what columns they see but also what they can update.

If you are exporting the query to Excel, arenl showed you how to build a custom select clause and that is fine because the query is not "live".