Can you count Columns in a query???

[pdbowling]

Hi, I've got a huge table subject to column addition on an ongoing basis. I pull data from the table and send it to an Excel spreadsheet. I need to know how many columns are in the table so I can select the appropriate regions for a graph (in Excel). Is there a query keyword for this? If not, is there a slick trick equivalent to counting columns?
Thanks all
PB

 

[coco86]

You could create a recordset based on the query and then determine the value of recordset.fields.count

 

[mikevh]

I don't know of a built-in way, but here's a function you could paste into a module and then call from a query.
In the query you'd reference it as ColCount("YourTableName&quot.
Note the name of the table would need to be quoted in
the call.

Public Function ColCount(tblName As String) As Integer
'Returns number of columns in table "tblName", 0 if table doesn't exist
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    
    Set db = CurrentDb()
    
    ColCount = 0
    For Each tbl In db.TableDefs
        If tbl.Name = tblName Then
            ColCount = tbl.Fields.Count
            Exit Function
        End If
    Next
End Function

 

[pdbowling]

I'll give these a try thank you.
PB

 

[coco86]

I like your approach, mikevh. Here's a way to make it even simpler, though:

Public Function ColCount(tblName As String) As Integer
'Returns number of columns in table "tblName", 0 if table doesn't exist
        
    ColCount = 0
    
    ColCount = CurrentDb.TableDefs(tblName).Fields.Count

End Function

 

[mikevh]

That's close to what I had originally, coco86. But
that will give a runtime error if the table doesn't
exist. Good idea to check that the table name passed
in is a valid one.

 

[coco86]

Good point. On the other hand, if there are a lot of tables in the database and/or a lot of records being returned by the query, looping through all the tables could really bog things down.

An error handler would be in order. Something along the lines of:

Public Function ColCount(tblName As String) As Integer
'Returns number of columns in table "tblName", 0 if table doesn't exist
        
    ColCount = 0
    
    on error resume next

    ColCount = CurrentDb.TableDefs(tblName).Fields.Count

End Function

This would still return a zero if the table didn't exist.

Hope you didn't think I was stepping on your toes. I thought it was a great idea!

 

[mikevh]

Okay, I like that. More concise. I hadn't thought
of using "on error resume next".