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