Tricky Delete problem

  • Thread starter Thread starter Ida
  • Start date Start date
I

Ida

Hi there, I've got another tricky problem.

I have a table with 5 columns. Columns are called 1, 2, 3, 4, 5.

One case:

Columns 1 and 3 have numbers in them. Columns 2, 4 and 5 do not any
data at all.

I want to be able to delete columns 4 and 5 because those are the last
two columns without data. Although column 2 does not have data, I want
to keep it because it's before a column that does have data.

Another case might be that only column 2 has data. I then would want to
delete columns 3, 4 and 5. I want to keep column 1.

Any ideas?
 
Hello Ida,

What do you mean by "delete columns"? Does this mean you don't want to see
the output from those columns? I can't imagine that you would actually want
to delete the column from the table - in any case, that deletes the whole
column, irrespective of whether it's got anything in any of the individual
rows.

If you just do a SELECT * FROM [Table Name], then you'll get all of the
columns, and if columns 4 & 5 don't have anything in them, you won't get
anything. It may help if you could describe eactly what sort of output you
are looking for...
 
David said:
Hello Ida,

What do you mean by "delete columns"? Does this mean you don't want to see
the output from those columns? I can't imagine that you would actually want
to delete the column from the table - in any case, that deletes the whole
column, irrespective of whether it's got anything in any of the individual
rows.

If you just do a SELECT * FROM [Table Name], then you'll get all of the
columns, and if columns 4 & 5 don't have anything in them, you won't get
anything. It may help if you could describe eactly what sort of output you
are looking for...
Click to expand...

Well I could either delete them or not show them. Either way, if I were
to use SQL programming, I am unfamilar as to how to do loops and pass
variables in SQL.

For example, the example I gave you was just one case. But the code
needs to be able handle any future case. So in other words, I might
have 10 fields, or I might have 50 fields. I won't be able to manually
select each column if i I have 50 fields. I need the code to do it.

Plus, I just don't want to hide or delete all columns that have no
values. I want to hide or delete only the columns that have no values
AND neither do all of the columns after them.
 
Well I could either delete them or not show them. Either way, if I were
to use SQL programming, I am unfamilar as to how to do loops and pass
variables in SQL.
Click to expand...

Me too. I guess this explains why I wasn't understanding it - so you want to
output a line of data using code, rather than using a query to display the
results?

In theory, you should be able to loop around the fields in each record
returned with something like:
Dim oRS As Recordset
Dim iFld As Integer
Dim iCount As Integer
Dim oFld As Field

Set oRS = CurrentDb.OpenRecordset(sTableName, dbOpenTable)

iFld = 0
iCount = 0
For Each oFld In oRS.Fields
iCount = iCount + 1
If oFld.Value Is Not Null Then iFld = iFld + 1
Next oFld

That would give you the count of the last field with data in it, and then you
should be able to loop around the field until you get to that field. Unllike
that first bit, I don't have the code in a nearby database for me to post for
you - maybe someone else can continue with the next bit? Pseudocode-wise,
it'd look like:

For i = 1 to iCount
If i = 1 Then
sOutput = oRS.Fields(0)
Else
sOutput = sOutput & "," & oRS.Fields(i-1)
End If
Next i
write sOutput

or something like that...
 
Back
Top