Hide Columns where there is no value

[dale pinn via AccessMonster.com]

Ok I've designed a query based on Months. So there are 12 Months and Twelve
Columns. However the user sometimes only wants to see eg February and March
but the rest of the months still show up with 0. So I would like to know
what I can do to hide these columns because ultimately I want to export
this query find to excel without the zero columns.

 

[Michel Walsh]

Hi,

You use a Crosstab? base the crosstab on a query that would eliminate
the other unwanted month, rather that directly on the original table. I
assume the original table is normalized, so keeping just the desired month
should be trivial, such as WHERE Month(DateTimeValue) IN( 2, 3 ).



Hoping it may help,
Vanderghast, Access MVP

 

[dale pinn via AccessMonster.com]

Hi Michel

I think I need to learn how to use those Cross Queries as I've been putting
it off. I've given it a try so far and I just cannot get past the "Please
enter in many/1 row(s), One Column, and One Value." Is there not an easier
way with some VBA in place where one runs the query, runs through the data
found, decides after going through field(a)that the total value is nil,
flags it as True, If true then hides/don't show field/column. Then either
makes a table based on the query (having deleted the nil fields/columns,
then exports the table.

 

[Michel Walsh]

Hi,


If the work is already done, you can DCount() on each field, and if the
count is not zero, you have data:


Me.March.Visible = ( 0 <> DCount("March", "tableNameHere"))

or

Me.Controls("March").Visible = ( 0 <> DCount("March",
"tableNameHere"))


or


Dim MyList() As Variant
MyList= Array("Jan", "Feb", "March")
Dim z As Variant
Dim x As String


For Each z In MyList
x=CStr(z)
Me.Controls(z).Visible = ( 0<> DCount(z, "MyTable"))
Next z





Hoping it may help,
Vanderghast, Access MVP

 

[dale pinn via AccessMonster.com]

Hi Michel

Well I went another way and after many attempts I got it right. I created a
table using a query and then executed the following code :

Private Sub Command0_Click()
Dim db As DAO.Database
Dim rstorig As DAO.Recordset
Dim strDupName As String, strSaveName As String, strOrig As String
Dim a As Integer
Dim isit As Integer

Set db = CurrentDb()
Set rstorig = db.OpenRecordset("Autex Changes In Usage Rate For Export")
If rstorig.BOF And rstorig.EOF Then
MsgBox "No records to process"
Else
rstorig.MoveFirst
a = 1
strOrig = rstorig.Fields(a)
Do Until a = 12
isit = 0
Do Until rstorig.EOF
If strOrig = 0 Then
isit = isit + 1
rstorig.MoveNext
If rstorig.EOF Then
strOrig = 0
Else
strOrig = rstorig.Fields(a)
End If
Else: End If
If strOrig <> 0 Then
isit = 0
rstorig.MoveNext
If rstorig.EOF Then
strOrig = 0
Else
strOrig = rstorig.Fields(a)
End If
Else: End If
Loop
If isit > 1 Then
fieldtodelete = rstorig.Fields(a).Name
db.Close
Set db = CurrentDb()
db.TableDefs("Autex Changes In Usage Rate For Export")
..Fields.Delete (fieldtodelete)
a = a - 1
Else: End If
Set db = CurrentDb()
Set rstorig = db.OpenRecordset("Autex Changes In Usage Rate For
Export")
rstorig.MoveFirst
a = a + 1
strOrig = rstorig.Fields(a)
Loop

db.Close
Set rstdup = Nothing
Set rstorig = Nothing
Set db = Nothing
End If
End Sub

However I cheated a little on the "a" variable because I just did not have
the time to work it out but I used "a = a - 1" because it was skipping
fields. Mainly because everytime it reopened the database the value of "a"
was now a field over. But I'll work it out. Thanks for your help