customize query

[Guest]

I have a query that shows several fields (ie. first name, last name, acct #,
date, address, value). I would like to create a form whereby the user would
select only the fields (by selecting the approriate check box) they would
like to see once the query is ran. For example, a user may only want to see 3
out of the 6 fields available fields.

I would think something would be needed in the query design to turn the
"show" option on or off. Can this be done?

 

[Guest]

Try changing the SQL within the query using code, something like

Dim MySQL As String
MySQL = "Select "
MySQL = MySQL & IIf(Me.[CheckBox1]=True,"[first name] ,","")
MySQL = MySQL & IIf(Me.[CheckBox2]=True,"[last name] ,","")
MySQL = MySQL & IIf(Me.[CheckBox3]=True,"[acct #] ,","")
MySQL = MySQL & IIf(Me.[CheckBox4]=True,"[date] ,","")
MySQL = MySQL & IIf(Me.[CheckBox5]=True,"[address] ,","")
MySQL = MySQL & IIf(Me.[CheckBox6]=True,"[value] ,","")

MySQL = Left(MySQL ,Len(MySQL)-1) ' To remove the last ,
MySQL =MySQL & " From TableName"

Application.CurrentDb.QueryDefs("QueryName").SQL = MySQL
Docmd.OpenQuery "QueryName"

Note: I didn't try this code, also you need to change the field names

 

[Guest]

Not sure in what part of the original SQL that shows all fields I add and/or
change the SQL provided to?

 

[Guest]

You need to create a query (can be empty)
The code I provided you with, you can add it to the form using a button that
will assign the SQL to the query created above and the open the query with
the desired columns