Filter a table's columns

[e.badin]

Hello all,

I come yet again with a brain puzzleing problem (for me at least).

Sooo.... I have this table that gets created after reading a TXT file line by line. The reason I do it so is because the column names from my table always change.

Now, there are 3 columns in this table that stay the same, and the rest 100 columns (yes, I know, 100) that are consecutive dates.

Ex:


Columns: X,Y,Z, Mon_4_14,Tue_4_15...... etc

What I want is to be able is to create another table based on the original one but with only, let's say 20 consecutive days.

What I need is an idea on how to loop through checking the column names, and filter them in a "select into" query.

Thank you!

[jzwp11]

The consecutive date fields are an example of repeating groups which indicates that your database is not normalized. Each should be a record in table not a field. If each were a record, it would be easy to create a query to get the data you want.

[e.badin]

I am perfectly aware of that. The question still stands.

[jzwp11]

This code snippet from Allen Browne's site shows how to get the field names of a table

Function ShowFields(strTable As String)
    'Purpose:   How to read the fields of a table.
    'Usage:     Call ShowFields("Table1")
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTable)
    For Each fld In tdf.Fields
        Debug.Print fld.Name, FieldTypeName(fld)
    Next
    
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

You say you want to create another table using the field names, but do you want to create records with the dates (of the field names) or do you want a table with the same field names just a subset of the original?