>

OrdinalPosition Property

Applies To

Field Object.

Description

Sets or returns the relative position of a Field object within the Fields collection to which it is appended. For an object not yet appended to the Fields collection, this property is read/write.

Settings and Return Values

The setting or return value is an integer that specifies the numeric order of fields. The default is 0. The data type is Integer.

Remarks

For a Field object, use of the OrdinalPosition property depends on the object that contains the Fields collection that the Field object is appended to, as shown in the following table.

Object appended to

Usage

Index

Not supported

QueryDef

Read-only

Recordset

Read-only

Relation

Not supported

TableDef

Read/write


Generally, the ordinal position of an object that you append to a collection depends on the order in which you append the object. The first appended object is in the first position (0), the second appended object is in the second position (1), and so on. The last appended object is in ordinal position count –1, where count is the number of objects in the collection as specified by the Count property setting.

Using the OrdinalPosition property, you can specify an ordinal position for new Field objects that differs from the order in which you append those objects to a collection. This enables you to specify a field order for your tables, queries, and recordsets when you use them in an application. For example, the order in which fields are returned in a SELECT * query would be determined by the current OrdinalPosition property values.

You can permanently reset the order in which fields are returned in recordsets by setting the OrdinalPosition property to any positive integer.

Two or more Field objects in the same collection can have the same OrdinalPosition property value, in which case they will be ordered alphabetically. For example, if you have a field named Age set to 4 and you set a second field named Weight to 4, Weight is returned after Age.

You can specify a number that is greater than the number of fields – 1. The field will be returned in an order relative to the largest number. For example, if you set a field's OrdinalPosition property to 20 (and there are only 5 fields) and you've set the OrdinalPosition property for two other fields to 10 and 30, respectively, the field set to 20 is returned between the fields set to 10 and 30.

After you change the OrdinalPosition property setting, you can use the property to return the current setting. However, the fields aren't reordered until the Fields collection is repopulated. For example:


TableDefs("Titles").Fields(0).OrdinalPosition = 3
This code changes which Field object is referenced in the fourth position (0 relative). Because Field(0) was Title, Field(2) now refers to Title, and Field(0) refers to the first (lowest) field in the sequence.

It is simpler to reference Field objects by their Name property. For example, this code assigns the AU_ID field to the third field position:


TableDefs("Titles").Fields("AU_ID").OrdinalPosition = 2

Note

Changing the OrdinalPosition property setting of a Field object in the Fields collection will not immediately change its position in the collection. You will need to use the Refresh method on the collection to force the Field into its new position. Further, the OrdinalPosition setting is not the same as the field's index within a collection, although they may be equal.

See Also

Count Property, QueryDef Object, Refresh Method.

Example (Microsoft Access)

The following example changes the setting of the OrdinalPosition property for the first field in a Products table.


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

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Products
    ' Return Field object pointing to first field in table.
    Set fldFirst = tdf.Fields(0)
    ' Set OrdinalPosition property to last position in collection.
    fldFirst.OrdinalPosition = tdf.Fields.Count
    ' Refresh Fields collection.
    tdf.Fields.Refresh
    ' Enumerate all fields and print ordinal position.
    For Each fld In tdf.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next fld
End Sub