How to sum columns with code?

[idta@hotmail.com]

Hi there, I have a table full of numbers. I want to delete all the
columns that do not have ANY numbers in the rows under the column. I
assume I could sum the column, and if the sum is 0, I know there are no
vaules in the column. I then could delete this column and move on to
the next column.

Anybody know the commands to sum the column, and proceed to the next
one (without knowing the name of the next column)?

I'd like to do this with code and not with a query.

-Victor

[Salad]

Re: How to sum columns with code?

idta@hotmail.co m wrote:[color=blue]
> Hi there, I have a table full of numbers. I want to delete all the
> columns that do not have ANY numbers in the rows under the column. I
> assume I could sum the column, and if the sum is 0, I know there are no
> vaules in the column. I then could delete this column and move on to
> the next column.
>
> Anybody know the commands to sum the column, and proceed to the next
> one (without knowing the name of the next column)?
>
> I'd like to do this with code and not with a query.
>
> -Victor
>[/color]
What if you have a record with a -1 and another with 1. Summing returns 0.

You might want to use Dcount instead. First, you might want to get the
record count for the table. Ex:
lngTableCnt = dcount("FieldNa me","TableName" )

Then get the count for 0/null
lngCnt = Dcount("FieldNa me","TableName" ,_
"IsNull(FieldNa me) Or FieldName = 0")

THen compare
If lngTableCnt = lngCnt then
'delete the field name
Endif

From Help, this is an example of how to remove a column
Dim dbs As Database

Set dbs = Currentdb

' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"

dbs.Close


I Modified slightly some code in Help that loops through fields

Sub NewField()
Dim dbs As Database, tdf As TableDef
Dim fld As Field

' Return reference to current database.
Set dbs = CurrentDb

' Return reference to Employees table. Change to your table name
Set tdf = dbs.TableDefs!E mployees
For Each fld In tdf.Fields
'select numeric fields
Select Case fld.Type
Case dbLong, dbCurrency, dbSingle, dbDouble, dbInteger, _
dbLong, dbByte, dbFloat, dbNumeric
Debug.Print fld.name & " " & fld.Type & " " & fld.Size
End Select
Next fld
Set dbs = Nothing
MsgBox "Done"
End Sub


You didn't say if you could program or not. If you can, the above
should be sufficient to get you going. Word of warning. Make a backup
copy of the tables before you start deleting columns.