programatically identifying blank fields in a table

[KSH]

I import large excel datasheets that I do data scrubbinb on. After I import
the data into a table, I would like to write a program that will look at each
field in the table and if there is not data in the field either delete it or
just print a report with the fields that have no data. Is there a way to use
a fields collection to look at every field? I am not sure where to start
looking if this is possible.

 

[Allen Browne]

You could loop through the Fields of the TableDef, to buid up a string like
this:
([MyField] Is Null) AND ([Field2] Is Null) AND ([AnotherField] Is Null)
AND ...

Once you achieve that, it's easy enough to get rid of those rows:
strSql = "DELETE FROM MyTable WHERE " & whatyouseeabove
dbEngine(0)(0).Execute strSql, dbFailOnError

There's a limit of about 50 ANDs in a WHERE clause, but surely if the first
50 columns are blank, there's not much there?

If you're not sure how to extract the Fields from the TableDef, there's an
example at:
http://members.iinet.net.au/~allenbrowne/func-06.html

 

[KSH]

Allen,

Thanks, but I don't want to just delete the rows, I want to delete the field
from the table if there is no data in any of the records. If I loop through
the tabledef can I use the fld.name in a query that checks if there is
anything in that field?

You could loop through the Fields of the TableDef, to buid up a string like
this:
([MyField] Is Null) AND ([Field2] Is Null) AND ([AnotherField] Is Null)
AND ...

Once you achieve that, it's easy enough to get rid of those rows:
strSql = "DELETE FROM MyTable WHERE " & whatyouseeabove
dbEngine(0)(0).Execute strSql, dbFailOnError

There's a limit of about 50 ANDs in a WHERE clause, but surely if the first
50 columns are blank, there's not much there?

If you're not sure how to extract the Fields from the TableDef, there's an
example at:
http://members.iinet.net.au/~allenbrowne/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I import large excel datasheets that I do data scrubbinb on. After I
import
the data into a table, I would like to write a program that will look at
each
field in the table and if there is not data in the field either delete it
or
just print a report with the fields that have no data. Is there a way to
use
a fields collection to look at every field? I am not sure where to start
looking if this is possible.

 

[Allen Browne]

Ah: you want to delete any column that has no data in any record.

Use DLookup() to look up the primary key of the table where the field is not
null. If that returns a Null, then there is no record where the field has a
value. This kind of thing:
strField = "MyField"
If IsNull(DLookup("ID", "MyTable", strField & " Is Not Null)) Then

In that case you want to DROP the column from the table:
strSql = "ALTER TABLE [MyTable] DROP COLUMN " & strField & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

Again, you may need to loop through the Fields of the TableDef if you want
to programmatically examine each column in turn.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks, but I don't want to just delete the rows, I want to delete the
field
from the table if there is no data in any of the records. If I loop
through
the tabledef can I use the fld.name in a query that checks if there is
anything in that field?

You could loop through the Fields of the TableDef, to buid up a string
like
this:
([MyField] Is Null) AND ([Field2] Is Null) AND ([AnotherField] Is
Null)
AND ...

Once you achieve that, it's easy enough to get rid of those rows:
strSql = "DELETE FROM MyTable WHERE " & whatyouseeabove
dbEngine(0)(0).Execute strSql, dbFailOnError

There's a limit of about 50 ANDs in a WHERE clause, but surely if the
first
50 columns are blank, there's not much there?

If you're not sure how to extract the Fields from the TableDef, there's
an
example at:
http://members.iinet.net.au/~allenbrowne/func-06.html

I import large excel datasheets that I do data scrubbinb on. After I
import
the data into a table, I would like to write a program that will look
at
each
field in the table and if there is not data in the field either delete
it
or
just print a report with the fields that have no data. Is there a way
to
use
a fields collection to look at every field? I am not sure where to
start
looking if this is possible.

 

[Jamie Collins]

There's a limit of about 50 ANDs in a WHERE clause

What makes you say that? The following works for me:

CREATE TABLE A (F INT NULL)
;
SELECT F FROM A WHERE F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0
AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0
AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0
AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0
AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0
AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND
F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0 AND F=0;

Maybe you are hitting another limit. Please post your code.

Jamie.

--

 

[Allen Browne]

Okay, Jamie, the actual limit of ANDs in a WHERE/HAVING clause depends on
the version of Access. It is 40 in Access 97 and earlier, and the current
version allows 99.

The point for this thread is that the limit is lower than the number of
potential fields in the table, which may restrict the usefulness of the
suggested technique, expecially if importing flatfile data.

 

[KSH]

Allen,

THANK YOU!!!!!!!!!!!!!! That did exactly what I was looking for, it will
save me loads of time.

Ah: you want to delete any column that has no data in any record.

Use DLookup() to look up the primary key of the table where the field is not
null. If that returns a Null, then there is no record where the field has a
value. This kind of thing:
strField = "MyField"
If IsNull(DLookup("ID", "MyTable", strField & " Is Not Null)) Then

In that case you want to DROP the column from the table:
strSql = "ALTER TABLE [MyTable] DROP COLUMN " & strField & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError

Again, you may need to loop through the Fields of the TableDef if you want
to programmatically examine each column in turn.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks, but I don't want to just delete the rows, I want to delete the
field
from the table if there is no data in any of the records. If I loop
through
the tabledef can I use the fld.name in a query that checks if there is
anything in that field?

You could loop through the Fields of the TableDef, to buid up a string
like
this:
([MyField] Is Null) AND ([Field2] Is Null) AND ([AnotherField] Is
Null)
AND ...

Once you achieve that, it's easy enough to get rid of those rows:
strSql = "DELETE FROM MyTable WHERE " & whatyouseeabove
dbEngine(0)(0).Execute strSql, dbFailOnError

There's a limit of about 50 ANDs in a WHERE clause, but surely if the
first
50 columns are blank, there's not much there?

If you're not sure how to extract the Fields from the TableDef, there's
an
example at:
http://members.iinet.net.au/~allenbrowne/func-06.html


I import large excel datasheets that I do data scrubbinb on. After I
import
the data into a table, I would like to write a program that will look
at
each
field in the table and if there is not data in the field either delete
it
or
just print a report with the fields that have no data. Is there a way
to
use
a fields collection to look at every field? I am not sure where to
start
looking if this is possible.

 

[Jamie Collins]

the actual limit of ANDs in a WHERE/HAVING clause depends on
the version of Access. It is 40 in Access 97 and earlier, and the current
version allows 99.

What do you mean 'in Access 97'? I can use the 99 ANDs with a Jet 3.51
..mdb (Access97 format, correct?) if I use the Jet 4.0 provider and I
can do this using in Access97 via VBA. If I use the Jet 3.51 provider
the query becomes 'too complex'. So I don't think the version of MS
Access nor the format of the .mdb is the limitation here. Any further
thoughts?

The point for this thread is that the limit is lower than the number of
potential fields in the table

Yes, I apologize for going slightly off-thread. But I'm trying to
understand a point you made. Many thanks for your continuing
assistance.

Jamie.

--