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 (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a Boolean that is True if the fields with Null values don't have an index entry. 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.

Remarks

To speed up the process of searching for records, you can define an index for a 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.

If IgnoreNulls is	And Required is	Then

True	False	A Null value is allowed in the index field; no index entry added.
False	False	A Null value is allowed in the index field; index entry added.
True or False	True	A Null value isn't allowed in the index field; no index entry added.

See Also

Required property.

Example

This example sets the IgnoreNulls property of a new Index to True or False based on user input, and then demonstrates the effect on a Recordset with a record whose key field contains a Null value.

Sub IgnoreNullsX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxNew As Index
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind!Employees

    With tdfEmployees
        ' Create a new Index object.
        Set idxNew = .CreateIndex("NewIndex")
        idxNew.Fields.Append idxNew.CreateField("Country")

        ' Set the IgnoreNulls property of the new Index object
        ' based on the user's input.
        Select Case MsgBox("Set IgnoreNulls to True?", _
            vbYesNoCancel)
            Case vbYes
                idxNew.IgnoreNulls = True
            Case vbNo
                idxNew.IgnoreNulls = False
            Case Else
                dbsNorthwind.Close
                End
        End Select

        ' Append the new Index object to the Indexes
        ' collection of the Employees table.
        .Indexes.Append idxNew
        .Indexes.Refresh
    End With

    Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        ' Add a new record to the Employees table.
        .AddNew
        !FirstName = "Gary"
        !LastName = "Haarsager"
        .Update

        ' Use the new index to set the order of the records.
        .Index = idxNew.Name
        .MoveFirst

        Debug.Print "Index = " & .Index & _
            ", IgnoreNulls = " & idxNew.IgnoreNulls
        Debug.Print "    Country - Name"

        ' Enumerate the Recordset. The value of the
        ' IgnoreNulls property will determine if the newly
        ' added record appears in the output.
        Do While Not .EOF
            Debug.Print "        " & _
                IIf(IsNull(!Country), "[Null]", !Country) & _
                " - " & !FirstName & " " & !LastName
            .MoveNext
        Loop

        ' Delete new record because this is a demonstration.
        .Index = ""
        .Bookmark = .LastModified
        .Delete
        .Close
    End With

    ' Delete new Index because this is a demonstration.
    tdfEmployees.Indexes.Delete idxNew.Name
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

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