Alter table Yes/No field

[Cojack]

Hi Everybody,

How do I add an Yes/No field into my Access via Alter statement.

ALTER TABLE TABLE1
ADD CHECKED YesNo;

Above sql add a collum to my table with Yes/No type.


However databaseview of the table show me 0 and -1 but i would like to have the checkboxes.

Regards,

[weekend00]

if you want to change it to checkbox format, go t design view and change it.

If you want to chagne in VBA code, you can try using tabledef to add fields (I've never tried, so I cann't tell you how).

[ajetrumpet]

weekend,

I don't know if this is thread hijacking or not yet because it's so old, but I thought I would drop a line and give the syntax for adding fields to a table via code:

dim db as database
set db = currentdb   
Dim tbldef As DAO.TableDef
Set tbldef = db.TableDefs(tablename)

   With tbldef
      .Fields.Append .CreateField(newfieldName, fieldtype (example - 'dbText'))
   End With

db.TableDefs.Refresh

Set tbldef = Nothing

[weekend00]

weekend,

I don't know if this is thread hijacking or not yet because it's so old,

The date is 10/19/2010

[ajetrumpet]

The date is 10/19/2010

it was 'kind of' a joke, sir. At any rate though, now you have the code you need. woohoo!

[weekend00]

Can we set the Display Control to checkBox instead of default TextBox? like:

field.displayControl = acCheckBox

[weekend00]

after 100 times trying, I modify your code as:

dim db as database
set db = currentdb   
Dim tbldef As DAO.TableDef
dim fld as dao.field
Set tbldef = db.TableDefs(tablename)
   With tbldef
       fld=.CreateField(newfieldName, "yesno") 
       fld.properties("DisplayControl")=accheckBox
      .Fields.Append fld
   End With
 
db.TableDefs.Refresh
 
Set tbldef = Nothing

[weekend00]

Another way.

after add column, change the display control type:

docmd.runsql "ALTER TABLE TABLE1 ADD CHECKED YesNo;"
currentdb.TableDefs("table1").Fields("checked").pr operties("DisplayControl")=acCheckBox