Create Table Yes/No Field (1 Viewer)

[Jaye7]

I have the following vba script and I can not get the yes/no field to work, also can you please help with adding field properties of required = yes and the text field to be 50, just examples are fine and I will update what I want. Thanks

 Sub test()
    
     Set Db = Application.CurrentDb
     Set tdf = Db.TableDefs("1Testing") ' 1Testing is your table name
     
      ' First create a field with data type = Text
     Set fld1 = tdf.CreateField("FieldT1", dbText) ' Field1 is your Field Name and formatted as text
     Set fld2 = tdf.CreateField("FieldT2", dbDate) ' Field2 is your Field Name and formatted as date/time
     Set fld3 = tdf.CreateField("FieldT3", dbMemo) ' Field3 is your Field Name and formatted as memo
     Set fld4 = tdf.CreateField("FieldT4", dbyes / no) ' Field4 is your Field Name and formatted as yesno
      
     With tdf.Fields
       .Append fld1 ' append first field
       .Append fld2 ' append second field
       .Append fld3 ' append third field
       .Append fld4 ' append fourth field
       .Refresh
     End With
     CreateField = True
 End Sub

 

[Jaye7]

Ok, so I used the following script to first set the field to text and then change it to yes/no.

 Set Db = Application.CurrentDb
     Set tdf = Db.TableDefs("1Testing") ' 1Testing is your table name
     
      ' First create a field with data type = Text
     Set fld1 = tdf.CreateField("FieldT1", dbText) ' Field1 is your Field Name and formatted as text
     Set fld2 = tdf.CreateField("FieldT2", dbDate) ' Field2 is your Field Name and formatted as date/time
     Set fld3 = tdf.CreateField("FieldT3", dbMemo) ' Field3 is your Field Name and formatted as memo
     Set fld4 = tdf.CreateField("FieldT4", dbText) ' Field4 is your Field Name and formatted as yesno
      
     With tdf.Fields
       .Append fld1 ' append first field
       .Append fld2 ' append second field
       .Append fld3 ' append third field
       .Append fld4 ' append fourth field
       .Refresh
     End With
     CreateField = True
     
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
     ' now change fld4 from text to yesno
     
     strSql = "ALTER TABLE " & "1Testing" & " ALTER COLUMN " & "FieldT4" & _
           " yesno;" ' changes the field type to single number, so can change single to double etc...
 Db.Execute strSql
 
 'Some Possible Data Types
    'YESNO (Boolean Value)
    'SINGLE (Number)
    'DOUBLE (Number)
    'INT (Long Integer)
    'SMALLINT (Integer)
    'TEXT
    'MEMO (Large Text for above 255 chars)
    'BINARY
 
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''