How to Change Field Names to Numbers

[Guest]

I have: various tables

I want: to change the field names to numbers, in sequential order from left
to right (e.g., if the fields in a table were Name, Address, and Phone, the
new field names should be 1, 2, and 3). Each table is different; some tables
may have three fields, while other may have fifty fields.

Any suggestions?

 

[Guest]

Hi DevDaniel,

I have to admit that I'm at a loss to quess why you would want to do this...
In order to share some methods with you, you'll have to provide more
information regarding context. That is, do you want to do this in a query or
perhaps you want to rename using DAO or ADO...

Luck
Jonathan

 

[Guest]

Hi Jonathan,

I want to make a query tool similar to the Access query tool: the user sees
a table and enters expressions for each field. Each field is separated by an
AND operator. Each record is separated by an OR operator.

If I build a SQL string, I need the field names. Unfortunately, the field
names are different with each table. I want to try changing the field names
to numbers, so they can be referenced in the SQL string.

The rough code would look something like this:

'Get expressions for each record
Do While Not rst.EOF

'Get expressions across the record
i = 1
For i = 1 To NumFields
strField="[Field" & i & "]"
If rst!strField <> "" Then
strSQLQuery = strSQLQuery & " AND " & rst!strField
End If
i = i +1
Next
rst.MoveNext
Loop

 

[Douglas J. Steele]

You'd need to use rst.Fields(strField), not rst!strField

However, you don't need to number the fields. You can determine the names of
the fields by looking at the Name property of the field.

For i = 1 To NumFields
strSQLQuery = strSQLQuery & " AND " & rst.Fields(i).Name
i = i +1
Next

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Jonathan,

I want to make a query tool similar to the Access query tool: the user
sees
a table and enters expressions for each field. Each field is separated by
an
AND operator. Each record is separated by an OR operator.

If I build a SQL string, I need the field names. Unfortunately, the field
names are different with each table. I want to try changing the field
names
to numbers, so they can be referenced in the SQL string.

The rough code would look something like this:

'Get expressions for each record
Do While Not rst.EOF

'Get expressions across the record
i = 1
For i = 1 To NumFields
strField="[Field" & i & "]"
If rst!strField <> "" Then
strSQLQuery = strSQLQuery & " AND " & rst!strField
End If
i = i +1
Next
rst.MoveNext
Loop

Hi DevDaniel,

I have to admit that I'm at a loss to quess why you would want to do
this...
In order to share some methods with you, you'll have to provide more
information regarding context. That is, do you want to do this in a query
or
perhaps you want to rename using DAO or ADO...

Luck
Jonathan

 

[John Nurick]

If I build a SQL string, I need the field names. Unfortunately, the field
names are different with each table. I want to try changing the field names
to numbers, so they can be referenced in the SQL string.

Like Jonathan I find it hard to imagine a situation in which this is
necessary or desirable.

It's trivially easy to retrieve the name of the field at a given
position in a recordset ready for use in your SQL statement:
NameOfFirstField = rsr.Fields(0).Name

It's equally easy to populate a combobox or listbox with a list of
fields in a table or query: just set the RowSourceType to "Field List".