Acc 2003 write SQL to only show columns if there is data in one record 1

[DougP]

Is there a way to write a SQL statement to only shows any column of a table if there is data in at least one record?
I often times have imported data that has dozens of empty columns

or even VBA code if it is a multi step process. I can create a function. Ultimatly it would be good to have a function where I just pass the table name and it spits out the results in a query.
ONly showing columsn that have data in them.

DougP

 

[SkipVought]

hi,

Are you referring to empty ROWS?

Please post an example to demonstrate your point.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Consider...

select ..., sum(len(field1),len(field2),len(field3),...)


having sum(len(field1),len(field2),len(field3),...)>0

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWise]

try

Function SqlNonNullFields(Tablename)
Dim mydb As Database
Dim rstFields As Recordset
Dim rst As Recordset
Dim SqlStr As Variant
Dim fld As Field
SqlStr = Null
Set mydb = CurrentDb
Set rstFields = mydb.OpenRecordset(Tablename)
For Each fld In rstFields.Fields
 Set rst = mydb.OpenRecordset("Select [" & fld.Name & "] From " & Tablename & " Where [" & fld.Name & "] is not null")
 If Not rst.BOF Then
    SqlStr = SqlStr + "," & fld.Name
 End If
Next
SqlStr = "Select " & SqlStr & "From " & Tablename
SqlNonNullFields = SqlStr
End Function

 

[DougP]

wow PWise, that worked fantastic!!! have a STAR
I copied and pasted teh SQL into a query.
is there a way to create a query using VBA code? and give it a default name?

DougP

 

[PWise]

Function SqlNonNullFields(NewQueryname, Tablename)
Dim mydb As Database
Dim rstFields As Recordset
Dim rst As Recordset
Dim SqlStr As Variant
Dim fld As Field
Dim qdf As QueryDef
SqlStr = Null
Set mydb = CurrentDb
Set rstFields = mydb.OpenRecordset(Tablename)
For Each fld In rstFields.Fields
 Set rst = mydb.OpenRecordset("Select [" & fld.Name & "] From " & Tablename & " Where [" & fld.Name & "] is not null")
 If Not rst.BOF Then
    SqlStr = SqlStr + "," & "[" & fld.Name & "]"
 End If
Next
SqlStr = "Select " & SqlStr & " From " & Tablename
Set qdf = mydb.CreateQueryDef("qryname", SqlStr)
End Function

 

[PWise]

sorry sb

Function SqlNonNullFields(NewQueryname, Tablename)
Dim mydb As Database
Dim rstFields As Recordset
Dim rst As Recordset
Dim SqlStr As Variant
Dim fld As Field
Dim qdf As QueryDef
SqlStr = Null
Set mydb = CurrentDb
Set rstFields = mydb.OpenRecordset(Tablename)
For Each fld In rstFields.Fields
 Set rst = mydb.OpenRecordset("Select [" & fld.Name & "] From " & Tablename & " Where [" & fld.Name & "] is not null")
 If Not rst.BOF Then
    SqlStr = SqlStr + "," & "[" & fld.Name & "]"
 End If
Next
SqlStr = "Select " & SqlStr & " From " & Tablename
Set qdf = mydb.CreateQueryDef(NewQueryname, SqlStr)
End Function

 

[DougP]

Oh yeah fantastic!!!
minor tweak, need brackets around the table name since my table names have spaces in them

here is my final one

Function SqlNonNullFields(Tablename)
    Dim mydb As Database
    Dim rstFields As Recordset
    Dim rst As Recordset
    Dim SqlStr As Variant
    Dim fld As Field
    Dim qry As QueryDef
    Dim qryObject As Object

    SqlStr = Null
    Set mydb = CurrentDb
    Set rstFields = mydb.OpenRecordset(Tablename)
    For Each fld In rstFields.Fields
      Set rst = mydb.OpenRecordset("Select [" & fld.Name & "] From [" & Tablename & "] Where [" & fld.Name & "] is not null")
    If Not rst.BOF Then
        SqlStr = SqlStr + "," & fld.Name
    End If
    Next
    
    SqlStr = "Select " & SqlStr & " From [" & Tablename & "]"
    
    NewQueryname = "qry-" & Tablename
    
    Set qdf = mydb.CreateQueryDef(NewQueryname, SqlStr)
    
End Function

DougP

 

[Andrzejek]

And just to be picky...
Since this Function does not return any value (any more), why not simply change it to a Sub ...?

Have fun.

---- Andy