ALTER TABLE: Add a new boolean column with default value and checkbox

Question

What is the correct Access DDL query to add a Boolean datatype column to a table?

So far I've seen examples like the following...

ALTER TABLE MyTable ADD MyNewColumName BIT

but they do not seem to be 100% correct since

Access does not apply the checkbox control to the newly added column, and
the allowed values for that column seem to be 0 and -1

This question is not about setting the data type of the field, but about setting the default display control (your title is misleading). As @Remou says below, that's not something that's settable with DDL. — David-W-Fenton
– David-W-Fenton, Commented Mar 10, 2011 at 4:38

Patrick · Accepted Answer · 2011-03-09 13:04:29Z

7

In access the Yes/No data type is a logical field that can display yes/no, true/false, or on/off. When you look at VBA code the true and false constants are equivalent to -1 and 0.

If you use this field to populate a check box it will function properly.

You may be able to Change your alter statement to use "YESNO" as such:

ALTER TABLE mytable ADD mynewcolumn YESNO

That should give you the desired check box in the access table column.

answered Mar 9, 2011 at 13:04

Patrick

7,7327 gold badges42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fionnuala Over a year ago

It won't, I am afraid, the checkbox can only be got with DAO.

Fionnuala · Accepted Answer · 2011-03-09 14:17:17Z

5

A DAO example.

''Requires reference to Microsoft DAO 3.6 Object Library
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As Database
Dim strSQL As String


Set db = CurrentDb

''Create a table ...
strSQL = "Create Table tblLTD (TheYesNoCheck YesNo, TheYesNoCombo YesNo)"
db.Execute strSQL

''It is now in the table collection, so ...
Set tdf = db.TableDefs("tblLTD")

''Change the way the YesNo fields display.
''A Checkbox
Set fld = tdf.Fields("TheYesNoCheck")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld.Properties.Append prp

''A combobox
Set fld = tdf.Fields("TheYesNoCombo")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acComboBox)
fld.Properties.Append prp
''We will need a format
Set prp = fld.CreateProperty("Format", dbText, "Yes/No")
fld.Properties.Append prp

From: http://wiki.lessthandot.com/index.php/Add_a_Display_Control_(Checkbox,_Combobox)_to_a_YesNo_Field

answered Mar 9, 2011 at 14:17

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

1 Comment

Amy Over a year ago

For those of you who are looking to add a yes/no field to a table in Access using VBA this code got me most of the way there. This worked for me: Set fld = tbl.CreateField(strFieldName, dbBoolean) tbl.Fields.Append fld Set prp = fld.CreateProperty("Format", dbText, "Yes/No") fld.Properties.Append prp fld.DefaultValue = "No"

Collectives™ on Stack Overflow

ALTER TABLE: Add a new boolean column with default value and checkbox

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related