Hide Column/Field In query based on checkbox (1 Viewer)

[KathCobb]

I have a multiple table query that is initiated from a Select form. I have four combo boxes, two option buttons as of right now. This is to filter down what is included in the results.

My query has fields for what will show up. I have three fields, that are used rarely but do sometimes have data that needs to be shown.

What I am trying to do is if the checkbox on the form is checked (True) then make the column in this query visible. Ive tried a couple criteria in the query filed such as:

IIf([Forms]![frmSelectToExport]![chkboxTrustInfo],True,[tblTrustAnnu]![TrustAmount].[Visible]=True)

Ive tried also referencing the query [qryListExport]![TrustAmount].[Visible]=True)

I've tried column hidden= false. Any variation I could come up with. All it does is return no values at all in the query and the column does not show up. I tried a little vba also but wasn't sure how to refer to the query going to data sheet view.

Is this even a possibility to do or am I way off base?

Thank you
Kathy

 

[plog]

Is this even a possibility to do or am I way off base?

Not possible, but not off base.

You cannot acheive what you want in a query object. You can achieve what you want with a report. What you would do is create a report based on your query (you can even make it look just like the query). Add a button on your form that says 'Run Report'. Then via VBA code you can apply criteria to your report and even hide fields based on input from the form when that button is clicked.

 

[June7]

Not sure how fields would be hidden on report. Fields are displayed in textboxes and textboxes not visible will still occupy space and if arranged horizontally will be white space. Why bother with code? If there is no data the textbox will be white space anyway.

If arranged horizontally, can set textbox height to 0 (will look like a line) and position textboxes against each other. Set CanGrow and CanShrink properties to Yes. There will still be some space occupied but not much.

 

[GinaWhipp]

@June7

Actually, if you are exporting to Excel this is quite handy. You give Users the opportunity to select what data they want to see in the Worksheet.

 

[KathCobb]

Ultimately, I will be bypassing any display version and exporting directly into excel.

Its more for the user who gets to excel and now has a bunch of empty columns. The easier I could make it for them the better.

So is it still a "no" cannot be done? A different post referenced using Screen.ActiveDatasheet along with some code but I didn't know how to apply that to my criteria

 

[KathCobb]

@June7

Actually, if you are exporting to Excel this is quite handy. You give Users the opportunity to select what data they want to see in the Worksheet.

Yes Gina....how do I do that?

 

[June7]

I don't know how to 'hide' fields/textboxes on report so there is no white space horizontally. I have used QueryDefs code to modify query to facilitate custom export to Excel. Generally, I don't like to implement code that modifies db design. Creating/Deleting/Modifying objects is changing design.

 

[GinaWhipp]

Well, you actually need to *build the query* as ultimately it is the query that gets exported. I seem to remember a sample database that did just this by Candace Tripp that did that...
http://www.candace-tripp.net/Pages/Main.aspx?cat=demo

Could be there are more out there, the key is you are trying to build a query for export so for now forget about the Form. Google is your friend! (Unless someone comes by with something else.)

 

[KathCobb]

I have the query. The query works fine until I try to add this visible not visible option. If I export the query As Is to Excel, I would have three columns of empty data, if that type of policy is not included in the search. I am trying to get rid of those columns. I will review the sample, just wondering if what you are saying means that I will somehow be able to say "on Export" if blank leave these columns out?

 

[June7]

If it will always be the same fields that should be excluded, build a query object that is used just for this export. Now complication arises if you need to apply filter criteria and likely will need dynamic parameters in the query.

 

[GinaWhipp]

Wait, are the empty columns ALWAYS empty? Or do you want to select which ones not to export? There's a difference and requires two different methods. So... which is it?

 

[KathCobb]

Ok...let me offer more detail. My query is to eventually "on click" in the search form, export directly to excel.

The query shows Agents, Clients, Policies. For each policy, there are different fields that are used. One policy, the "trusts", have three fields that only apply to them. Yes, these fields are in their own separate table.

So 90% of the time, when someone runs this query they are going to select the following:

Agent: John Doe
County: Some Name
Company: Medical Co
Plan: Their Plan

And the options chosen will have approximately 20 columns (fields) of info completed. If on that 5% of the time that Plan choice happens to be "Trust" there will now be 23 columns of useful information. What I am trying to do, is when Trust is not chosen, instead of 3 empty columns at the end, they just would not be there.

Is that possible?

 

[MajP]

For the first part of just displaying this you can do it with a datasheet. Here is an example with a listbox and a subform with a datasheet. Click in the listbox and show/hide the selected columns.

Private Sub lstChoose_AfterUpdate()
  Call hideColumns
End Sub

Public Sub hideColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim i As Integer
  Set frm = Me.subFrmProducts.Form
  Set lst = Me.lstChoose
  Call showColumns
  For Each itm In lst.ItemsSelected
    frm.Controls(lst.ItemData(itm)).ColumnHidden = -1
  Next itm
End Sub
Public Sub showColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim i As Integer
  Set frm = Me.subFrmProducts.Form
  Set lst = Me.lstChoose
  For i = 0 To lst.ListCount - 1
    frm.Controls(lst.ItemData(i)).ColumnHidden = 0
  Next i
End Sub

 

[MajP]

Some modification of this should work. Pass in the name of the query and the fields you want to show. If you have a multiselect listbox and pass the field names in it should work well.

Public Sub OpenQryShowFields(qryname As String, ParamArray ShowFields() As Variant)
  Dim qdf As QueryDef
  Dim i As Integer
  Dim strSelect As String
  'Have a temp query def that you reuse
  Set qdf = CurrentDb.QueryDefs("qryTemp")
  'Grab the sql from the query you want to show fields
  qdf.Sql = CurrentDb.QueryDefs(qryname).Sql
   For i = 0 To UBound(ShowFields)
    If strSelect = "" Then
      strSelect = "Select " & ShowFields(i)
    Else
      strSelect = strSelect & ", " & ShowFields(i)
    End If
  Next i
  qdf.Sql = strSelect & " FROM " & qryname
  Debug.Print qdf.Sql
  
  DoCmd.OpenQuery qdf.Name
End Sub
Public Sub ShowQuery()
   OpenQryShowFields "qryTest", "EmployeeID", "LastName", "FirstName"
End Sub

 

[KathCobb]

I will try these options tomorrow, but I am trying to avoid list boxes because there are too many choices. Will this work if it is a combo box ?

Thank you for all the help so far.

 

[MajP]

Those solutions were to be able to select any columns to show or hide. Rereading you last post it seems much simpler than that. Sounds to me like you just need to make a couple of different queries based on your query. Choice "No Trust" is queryA, and Choice "Trust" is queryB. No reason to dynamically show and hide columns.

 

[KathCobb]

I was trying to do it with a checkbox. Is there a way that if the box was checked, I could run the trust query you suggest?

Maybe I am trying to make this more complicated than it needs to be. Once exported to excel, they can just delete the columns....

Below is a post from another website...could this apply to me:

You do it exactly the same way you would on a datasheet form --
Screen.ActiveDatasheet returns a form object, so you refer to the
columns like controls on a form:
Screen.ActiveDatasheet!MyFieldToHid.ColumnHidden = True
Note that only ! syntax works here, not . syntax (I have no idea
why, but I laugh in evil glee at the fact, since I've never liked .
syntax).
That's my point -- it's really really simple to do this, as it works
almost exactly the same as with a form.

 

[GinaWhipp]

That is why I asked the asked my question. Because you can just delete the blank columns provided you know which ones they are though you could check if they are blank.

Wait, you posted this someplace else? Hmm...

 

[KathCobb]

I did Not post this anywhere else....I found that in a google search before I asked here, but I do not understand it and/or if it would work for me somehow.

And yes, the columns can be deleted in excel...I won't be the one running the query and working with the spreadsheet afterwards though. Those who will are not very computer savvy but definitely very complaining savvy, so I was trying to eliminate/account for all issues.

I was really hoping to find a solution that basically said, if this checkbox is checked, then include these columns. If not do not include these columns.
That is why I was wondering how/if that could apply to using separate queries as suggested here or that data sheet google I found.

 

[CJ_London]

hiding a column wont work if the OP wants to export the query - it will export the whole query, columns hidden or not.

The only way to do this is to use vba to construct your query - omitting the columns that are not required.

perhaps something like

dim sqlStr as String
dim qdef as querydef
 
sqlStr="SELECT Agent, County, Company, Plan,"
if me.chk1=true then sqlStr=sqlStr & " fldx,"
if me.chk2=true then sqlStr=sqlStr & " fldy,"
if me.chk3=true then sqlStr=sqlStr & " fldz,"
sqlStr=sqlStr & " FROM myTable"
currentdb.createquerydef("tmp",sqlStr)
docmd.transferspreadsheet…..
docmd.deleteobject acQuery,"tmp"

code not checked for syntax or accuracy.