IgnoreNulls Property

Applies To

Index Object.

Description

Sets or returns a value that indicates whether records that have Null values in their index fields have index entries. This property is read/write for a new Index object not yet appended to a collection and read-only for an existing Index object in an Indexes collection.

Settings and Return Values

This property is a Boolean expression that indicates index entries. The data type is Boolean. True (-1) indicates that the fields with Null values don't have an index entry.

Remarks

To make searching for records using a field faster, you can define an index for the field. If you allow Null entries in an indexed field and expect many of the entries to be Null, you can set the IgnoreNulls property for the Index object to True to reduce the amount of storage space that the index uses.

The IgnoreNulls property setting and the Required property setting together determine whether a record with a Null index value has an index entry, as the following table shows.

IgnoreNulls	Required	Null in index field

True	False	Null value allowed; no index entry added
False	False	Null value allowed; index entry added
True or False	True	Null value not allowed; no index entry added

See Also

Required Property.

Example (Microsoft Access)

The following example adds a new index to a TableDef object, makes that index the current index, and invokes the Seek method based on that index. The names of the Field objects created and appended to the Fields collection of the Index object correspond to the names of current fields in the table.

Sub NewIndex()
    Dim dbs As Database, tdf As TableDef
    Dim idx As Index, fldID As Field, fldName As Field

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Products
    ' Return Index object that points to new index.
    Set idx = tdf.CreateIndex("IDName")
    ' Create and append index fields.
    Set fldID = idx.CreateField("ProductID")
    Set fldName = idx.CreateField("ProductName")
    idx.Fields.Append fldID
    idx.Fields.Append fldName
    ' Set index properties.
    idx.IgnoreNulls = True
    idx.Unique = True
    ' Append the new Index object to the Indexes collection.
    tdf.Indexes.Append idx
End Sub