Can a column be deleted with a query based on criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Back
Top