AllowZeroLength Property

Applies To Field object.

Description

Sets or returns a value that indicates whether a zero-length string (" ") is a valid setting for the Value property of the Field object with a Text or Memo data type.

Settings and Return Values

The setting or return value is a Boolean data type that indicates if a value is valid. The value is True if the Field object accepts a zero-length string as its Value property; the default value is False.

Remarks

For an object not yet appended to the Fields collection, this property is read/write.

Once appended to a Fields collection, the availability of the AllowZeroLength property depends on the object that contains the Fields collection, as shown in the following table.

If the Fields collection belongs to an	Then AllowZeroLength is

Index object	Not supported
QueryDef object	Read-only
Recordset object	Read-only
Relation object	Not supported
TableDef object	Read/write

You can use this property along with the Required, ValidateOnSet, or ValidationRule property to validate a value in a field.

See Also Required property, TableDef object, ValidateOnSet property, ValidationRule property, ValidationText property, Value property.

Example

In this example, the AllowZeroLength property allows the user to set the value of a Field to an empty string. In this situation, the user can distinguish between a record where data is not known and a record where the data does not apply.

Sub AllowZeroLengthX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim fldTemp As Field
    Dim rstEmployees As Recordset
    Dim strMessage As String
    Dim strInput As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs("Employees")
    ' Create a new Field object and append it to the Fields
    ' collection of the Employees table.
    Set fldTemp = tdfEmployees.CreateField("FaxPhone", _
        dbText, 24)
    fldTemp.AllowZeroLength = True
    tdfEmployees.Fields.Append fldTemp

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        ' Get user input.
        .Edit
        strMessage = "Enter fax number for " & _
            !FirstName & " " & !LastName & "." & vbCr & _
            "[? - unknown, X - has no fax]"
        strInput = UCase(InputBox(strMessage))
        If strInput <> "" Then
            Select Case strInput
                Case "?"
                    !FaxPhone = Null
                Case "X"
                    !FaxPhone = ""
                Case Else
                    !FaxPhone = strInput
            End Select

            .Update

            ' Print report.
            Debug.Print "Name - Fax number"
            Debug.Print !FirstName & " " & !LastName & " - ";

            If IsNull(!FaxPhone) Then
                Debug.Print "[Unknown]"
            Else
                If !FaxPhone = "" Then
                    Debug.Print "[Has no fax]"
                Else
                    Debug.Print !FaxPhone
                End If
            End If

        Else
            .CancelUpdate
        End If

        .Close
    End With

    ' Delete new field because this is a demonstration.
    tdfEmployees.Fields.Delete fldTemp.Name
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a new Field object and sets its AllowZeroLength property to True (–1):

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

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create new field in Employees table.
    Set fld = tdf.CreateField("SpouseName", dbText, 15)
    ' Allow zero-length strings in field.
    fld.AllowZeroLength = True
    ' Append Field object.
    tdf.fields.Append fld
    Set dbs = Nothing
End Sub