Automate the Show/Hide of a Column not in Use

[Vagus14]

Hello everyone,

I want to automate a query so when the user views it they see only the columns that have data. Columns that do not have data are not there. Is their a way to do this?
I am using MS 2007

My Access tool is setup to import data into tables and then the user views the data imported. However, if their's no data (and no column to import) I want the query report to not include the column.

Thanks!

 

[plog]

You posted this in the Queries sub-forum, but used the term "query-report", so its a little ambigous where you want this hiding to take place.

If its in a query object the answer is no. In a query object you cannot hide columns based on if they contain data or not. If the answer is in a report object, then the answer is yes, but its going to take some medium-advanced VBA coding to do it.

 

[Vagus14]

I am pretty decent in VBA but I don't know everything yet. So their's no way to hide a column that is null? Hmmm, That's too bad. Would of made things a lot easier.

Yes this is strickly Query! (I mentioned report because I use the query as a report to the user)

Alright so let me get a little more specific.
The table that is importing has the following columns: Qty Rec, Qty Rec1, Qty Rec2

Usually only the Qty Rec is filled out that's why I want to hide 1 and 2 if there's no data. Is their some VBA that could help out with this?

OR

I want to not include Qty Rec1 and Qty Rec2 when I DoCmd.Transferspreadsheet Export if the columns have no data in them. Would this be possible with VBA?

 

[plog]

I believe skipping a column when exporting the data would be possible. You would load it into a recordset, test each column to find out if everything is null, then skip any columns that contain no data. Again, medium-advanced level VBA stuff.

 

[Twincam]

How about...

pointing the user to a form rather than a query/report.

The form then runs the query
checks the columns
...

 

[Minty]

You could use a cross tab / pivot table - that will only display data if it exists if you format it correctly.

 

[Vagus14]

All good suggestions. Thank you! Can someone give me an example of a recordset (Skipping blank columns) and I can take it from there? Thanks for everyones help!

Oh and Minty, thank you also but I'm gonna try and avoid pivots. Maybe a cross-tab would work. I'll try and give that a shot also.

Gonna first try the record set VBA approach. Any example code you guys can shoot me off the top of you head?

 

[plog]

Here's the microsoft site for Open.Recordset: http://msdn.microsoft.com/en-us/library/office/ff820966(v=office.15).aspx

My method would be to dynamically create a SELECT statement to load the data. I'd start it with all the fields that will be shown no matter what, then add the fields that I want to show only if they had data:

str_SQL="SELECT FieldToAlwaysShow1, FieldToAlwaysShow2, FieldToAlwaysShow3...

If (DCount("NotNullField1", "YourTableNameHere") > 0) Then
    str_SQL = str_SQL & ", NotNullField1"
End If
If (DCount("NotNullField2", "YourTableNameHere") > 0) Then
    str_SQL = str_SQL & ", NotNullField2"
End If

etc...

(Not valid code, just example psuedo code)

That way you can dynamically create your SQL to pull the columns you need. Load the query into a recordset, and export the recordset.

 

[Vagus14]

Awesome. Thanks so much, I really appreciate the help and example! Happy holidays! I will give this a shot.