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 (Microsoft Access workspaces only).
Syntax
expression .AllowZeroLength
expression A variable that represents a Field object.
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
|
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