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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hi william007,

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

I think you can also use Logical instead of YesNo.


Pete
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

harfangCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.