AllowZeroLength 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