Tricky Delete problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ida - 16 Nov 2005 05:26 GMT

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?

Reply to this Message

David S - 17 Nov 2005 02:00 GMT

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...

Reply to this Message

Ida - 17 Nov 2005 03:55 GMT

> Hello Ida,
>
[quoted text clipped - 12 lines]
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200511/1

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.

Reply to this Message

David S - 18 Nov 2005 02:04 GMT

>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.

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...

Reply to this Message