I have a table in access that needs to be exported to an Excel spreadsheet. 
One of the requested design specifications for the resulting spreadsheet is 
that it not contain empty columns. (No columns with Null fields)
Currently I am manually deleting them in Excel and this is incredibly 
tedious, since there are about 80 columns of data and I am checking whether 
they are empty by using the auto-filter feature and observing the options 
that appear in the drop down menu.
Can I write a query that contains instructions to delete columns that are 
contain nothing but Nulls?

J,

You could write a function to build the SQL dynamically. This is untested, 
so you may have to play with it some.  You will obviously need to change the 
references to "TableName" to the name of the table you are using.  If you are 
using a query for this rather than a table, you need to set a reference to 
the QueryDef, not the TableDef.

Public Function fnDynamicSQL() as String

    dim strSQL as String, strCriteria as string
    dim strFieldName as String
    dim tdf as dao.tabledef
    dim fld as dao.field

    strSQL = "SELECT "
    
    'Open the tabledef and loop through the fields
    Set tdf = currentdb.tabledefs("TableName")
    for each fld in tdf.fields
        strCriteria = "[" & fld.Name & "] IS NOT NULL"

        'I started to use DCOUNT here, but think DLOOKUP will be much
        'quicker since it will look for the first non-NULL value, rather
        'than counting all of the non-null values
        if ISNULL DLOOKUP(fld.Name, "TableName", strCriteria) > 0 then
            strSQL = strSQL & " [" & fld.Name & "], "
        end if
    next
    Set tdf = nothing

    'strip the last comma and space from the SQL string
    strsql = left(strsql, len(strsql) - 2) & " FROM TableName"
     
    fnDynamicSQL = strsql

End Function

HTH
Dale

-- 
Email address is not valid.
Please reply to newsgroup only.


"jgraves" wrote:

> I have a table in access that needs to be exported to an Excel spreadsheet. 
> One of the requested design specifications for the resulting spreadsheet is 
> that it not contain empty columns. (No columns with Null fields)
> Currently I am manually deleting them in Excel and this is incredibly 
> tedious, since there are about 80 columns of data and I am checking whether 
> they are empty by using the auto-filter feature and observing the options 
> that appear in the drop down menu.
> Can I write a query that contains instructions to delete columns that are 
> contain nothing but Nulls?