title: MS Access Control Property.Type not making sense
body: <p>I have written code dealing with TableDef and Field properties in the past. It's not hard, it just involves looping through the Field.Properties collection and doing a certain amount of error checking along the way. Property.Name, Property.Type and Property.Value yield everything we need.</p>
  <p>The problem: I have just written some code to do the same kind of property enumeration for Form and Report controls. The property name and values come through fine, but the .Type is not correct. An example:</p>
  <pre><code>Public Sub TestPropTypes()
  Dim dbs As DAO.Database
          
      Set dbs = CurrentDb()
      
      Dim td As TableDef
      Dim fld As DAO.Field
      
      Set td = dbs.TableDefs(&quot;CalendarEvent_&quot;)
      Set fld = td.Fields(&quot;EventDescription&quot;)
      PrintObjectProps fld
      
      Dim f As Form
      Dim c As Control
  
      DoCmd.OpenForm &quot;fmCalendarDates&quot;, acDesign
      Set f = Forms!fmCalendarDates
      Set c = f.Controls(&quot;Label7&quot;)
      PrintObjectProps c
  End Sub
  
  Private Sub PrintObjectProps(c As Object, Optional RecursionDepth As Integer = 0)
  Dim ExistingProperty As DAO.Property
  Dim PropCount As Integer
  Dim GotValue As Boolean
  Dim v As Variant
  
      Debug.Print c.Name
      
      For Each ExistingProperty In c.Properties
          If PropCount &gt; 12 Then Exit Sub
      
          GotValue = True
          On Error Resume Next
          v = ExistingProperty.Value
          If Err.number &lt;&gt; 0 Then GotValue = False
          On Error GoTo 0
  
          If GotValue Then
              Debug.Print &quot;  &quot; &amp; ExistingProperty.Name &amp; &quot; &quot; _
                  &amp; GetFieldDDLTypeName(ExistingProperty.Type) &amp; &quot;(&quot; &amp; ExistingProperty.Type &amp; &quot;) &quot; _
                  &amp; xf.Gen.dq(CStr(ExistingProperty.Value))
          End If
          
          PropCount = PropCount + 1
      Next
  End Sub
  
  Public Function GetFieldDDLTypeName(FieldType As DAO.DataTypeEnum) As String
  Dim rtnStr As String
      ' as per: http://allenbrowne.com/ser-49.html
      
      Select Case FieldType
          Case dbBoolean: rtnStr = &quot;YESNO&quot;
          Case dbByte: rtnStr = &quot;BYTE&quot;
          Case dbInteger: rtnStr = &quot;SHORT&quot;
          Case dbLong: rtnStr = &quot;LONG&quot;
          Case dbCurrency: rtnStr = &quot;CURRENCY&quot;
          Case dbSingle: rtnStr = &quot;SINGLE&quot;
          Case dbDouble: rtnStr = &quot;DOUBLE&quot;
          Case dbDate: rtnStr = &quot;DATETIME&quot;
          Case dbBinary: rtnStr = &quot;BINARY&quot;
          Case dbText: rtnStr = &quot;TEXT&quot;
          Case dbLongBinary: rtnStr = &quot;LONGBINARY&quot;
          Case dbMemo: rtnStr = &quot;MEMO&quot;
          Case DBGuid: rtnStr = &quot;GUID&quot;
      End Select
      
      GetFieldDDLTypeName = rtnStr
  End Function
  </code></pre>
  <p>Yields:</p>
  <pre><code>TestPropTypes
  EventDescription
    Attributes LONG(4) &quot;2&quot;
    CollatingOrder SHORT(3) &quot;1033&quot;
    Type SHORT(3) &quot;10&quot;
    Name MEMO(12) &quot;EventDescription&quot;
    OrdinalPosition SHORT(3) &quot;2&quot;
    Size LONG(4) &quot;100&quot;
    SourceField MEMO(12) &quot;EventDescription&quot;
    SourceTable MEMO(12) &quot;CalendarEvent_&quot;
    DataUpdatable YESNO(1) &quot;False&quot;
    DefaultValue MEMO(12) &quot;&quot;
  Label7
    EventProcPrefix DATETIME(8) &quot;Label7&quot;
    Name DATETIME(8) &quot;Label7&quot;
    ControlType BYTE(2) &quot;100&quot;
    Caption DATETIME(8) &quot;Description&quot;
    Visible LONGBINARY(11) &quot;True&quot;
    Width BYTE(2) &quot;1875&quot;
    Height BYTE(2) &quot;285&quot;
    Top BYTE(2) &quot;425&quot;
    Left BYTE(2) &quot;1048&quot;
    BackStyle BYTE(2) &quot;0&quot;
    BackColor SHORT(3) &quot;16777215&quot;
    BorderStyle BYTE(2) &quot;0&quot;
    OldBorderStyle BYTE(2) &quot;0&quot;
  </code></pre>
  <p>So you can see the first group, from a Field in a TableDef, yields the kind of results we'd expect.</p>
  <p>The second group, using exactly the same rendering code, is from a control on a form. All the MEMO properties are coming through as DATETIME, all the YESNO as LONGBINARY, etc. It's at least consistent, and I could probably deduce the type translation and write a conversion algorithm. I thought it might be using ADO values at first (because YESNO is 11), but there's no consistency with other ADO values and we are also explicitly using a DAO object, so that just wouldn't make sense.</p>
  <p>This is inconsistent, and I'd like to work out and document some kind of sensible solution.
  Has anyone come across this before ?</p>
  <p>Tested on Access 2003 and 2007, so I'm sure it's not a weird bug in my Access ver. It's an Access 2003 format database, so there are no extended types throwing a spanner in the works.</p>
  <p>EDIT: Problem is solved thanks to HansUp. Now we're checking out if the table/field versus form/report properties do use different return values. I offer this:</p>
  <pre><code>Public Sub TestPropTypes1()
  Dim prop As DAO.Property
  Dim dbs As DAO.Database
  Set dbs = CurrentDb()
  
      DoCmd.OpenForm &quot;fmCalendarDates&quot;, acDesign
      
      Debug.Print &quot;Property&quot;, &quot;Value&quot;, &quot;varType(.Value)&quot;, &quot;.Type&quot;
      Debug.Print
      
      Set prop = dbs.TableDefs(&quot;CalendarEvent_&quot;).Fields(&quot;EventDate&quot;).Properties(&quot;Name&quot;)
      Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
  
      Set prop = dbs.TableDefs(&quot;CalendarEvent_&quot;).Fields(&quot;EventDate&quot;).Properties(&quot;Required&quot;)
      Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
  
      Set prop = Forms!fmCalendarDates!Label7.Properties(&quot;Name&quot;)
      Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
  
      Set prop = Forms!fmCalendarDates!Label7.Properties(&quot;Visible&quot;)
      Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
  End Sub
  </code></pre>
  <p>yielding</p>
  <pre><code>TestPropTypes1
  Property      Value          vartype(prop.Value)   prop.Type
  Name          EventDate      8                     12 
  Required      False          11                    1 
  Name          Label7         8                     8 
  Visible       True           11                    11 
  </code></pre>
  <p>which would strongly suggest that the TableDef Field properties do indeed use the DAO.DataTypeEnum types, whereas the form properties appear to yield a VBA.VbVarType return.
  For example, the Required property of the field returns a type corresponding to the VbVarType of NULL, whereas it's a DataTypeEnum of YesNo.</p>
  <p>Note the subtle difference between prop.Type and varType(prop.Value)</p>
  <p>While we could use varType(prop.value), this would normally be considered bad practice because the type may depend on the contents of the value (eg. a Null), whereas the .Type is authoritative metadata. In the case of system properties like this, the values may be well behaved and there may not be a practical difference.</p>
  <p>The really surprising thing is that the reference materials make no mention of this issue whatsoever.</p>