IgnoreNulls Property

Applies To Index.

Description

You can use the IgnoreNulls property to specify that records with Null values in the indexed fields not be included in the index.

Setting

The IgnoreNulls property uses the following settings.

Setting	Description	Visual Basic

Yes	Records that contain Null values in the indexed fields aren't included in the index.	True (–1)
No	(Default) Records that contain Null values in the indexed fields are included in the index.	False (0)

You can set this property by using the Indexes window of table Design view or Visual Basic.

To access the IgnoreNulls property of an index by using Visual Basic, use the DAO IgnoreNulls property.

Remarks

You can define an index for a field to facilitate faster searches for records indexed on that field. If you allow Null entries in the indexed field and expect to have many of them, set the IgnoreNulls property for the index to Yes to reduce the amount of storage space that the index uses.

See Also IgnoreNulls property ("DAO Language Reference"), Index object ("DAO Language Reference"), Indexed property, Indexes collection ("DAO Language Reference"), Primary property, Primary property ("DAO Language Reference"), Unique property, Unique property ("DAO Language Reference").

Example

The following example adds a new index to a TableDef object and sets the IgnoreNulls and Unique properties to True (–1):

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

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Products table.
    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 new Index object to Indexes collection.
    tdf.Indexes.Append idx
    Set dbs = Nothing
End Sub