How to add a yes/no column using SQL syntax?

I have a table called "myTable", I wish to add a field "myField" which is yes/no data type using sql syntax.  How to do it?
LVL 9
william007Asked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
Hi william007,

currentdb.Execute "ALTER TABLE Employees ADD COLUMN StillAlive YesNo"

I think you can also use Logical instead of YesNo.


Pete
0
 
william007Author Commented:
But the result table is appear as 1 or 0.
I wish to let it appear as a checkbox, is it possible to do this via SQL?
0
 
jjafferrCommented:
Hi william007,
this will create the field in your Table, then assign a default value of True:

    dbs.Execute "ALTER TABLE myTable ADD COLUMN myField YESNO;"
    dbs.TableDefs("myTable").Fields("myField").DefaultValue = True

jaffer
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
harfangConnect With a Mentor Commented:
Hello,

> I wish to let it appear as a checkbox, is it possible to do this via SQL?

That is not possible. The checkbox display is part of the field properties used by Access, not of the actual field definition. SQL knows nothing  about controls (checkboxes, combos, etc).

You can however change is through code, using DAO. Try this in the immediate pane:
? CurrentDb.TableDefs("Products").Fields("Discontinued").Properties("DisplayControl")

If the property "DisplayControl" exists (for fields created through the Access interface), you will see 106 (acCheckBox), 109 (acTextBox), etc. You can just change the value. If the property doesn't exist, you will have to create it.
See CreateProperty in Access help.

For example, this will change the display type to combo:

    Dim fld As DAO.Field
    With CurrentDb
        Set fld = TableDefs("myTable").Fields("myField")
        SetProperty fld, "DisplayControl", acCheckBox, dbInteger
    End With

Using this VB module (basProperties):

-------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Function GetProperty(DAO_Object As Object, Name As String)
'
' Returns named property of the DAO object,
' or Null on error or property not found.
'
On Error Resume Next
    GetProperty = DAO_Object.Properties(Name)
    If Err Then
        Err.Clear
        DAO_Object.Properties.Refresh
        GetProperty = DAO_Object.Properties(Name)
        If Err Then Err.Clear: GetProperty = Null
    End If
   
End Function

Function SetProperty( _
    DAO_Object As Object, _
    Name As String, _
    Optional Value As Variant = Null, _
    Optional DataType As DAO.DataTypeEnum = dbText _
    ) As Boolean
'
' Sets the property or creates a new property
' If called without a value, the property is deleted.
'
    Dim prp As DAO.Property

' ongoing error checking:
On Error Resume Next

    ' delete property?
    If IsNull(Value) Then
        DAO_Object.Properties.Delete Name
        SetProperty = (Err = 0)
        DAO_Object.Properties.Refresh
        Err.Clear
        Exit Function
    End If

    ' get the property if it exists:
    Set prp = DAO_Object.Properties(Name)
    If prp Is Nothing Then
        Err.Clear
        ' create a new one.
        Set prp = DAO_Object.CreateProperty(Name, DataType, CStr(Value))
        If prp Is Nothing Then
            Err.Clear
            Exit Function
        End If
        ' and append
        DAO_Object.Properties.Append prp
        If Err Then Err.Clear: Exit Function
        DAO_Object.Properties.Refresh
       
    ElseIf prp.Value <> Value Then
        ' set to new value
        prp.Value = Value
        If Err Then
            Err.Clear
            Exit Function
        End If
    End If
   
    SetProperty = True

End Function
-------------------------------------------------------------------------------------------

Good luck!
(°v°)

0
 
william007Author Commented:
Thanks:-)
0
 
harfangCommented:
Welcome, and success with your application!
(°v°)
0
All Courses

From novice to tech pro — start learning today.