[AccessD] (Simple?) Problem Creating a "Yes/No" [Data Type] Field in a MakeTable Query

Hi Alan,

I never found a way in SQL because it's a display control
I had to do this in DAO after the CREATE TABLE statement.
Code is below.

Michael R. Mattys
Try MattysMapLib for MapPoint at
www.mattysconsulting.com



SetAccessProperty db.TableDefs("X").Fields("Y"), "DisplayControl", 3, True,
106

Private Function SetAccessProperty(obj As Object, strName As String, _
        intType As Integer, varSetting As Variant, Optional SetToWhat As
String) _
        As Boolean

    Dim prp As DAO.Property
    Const conPropNotFound As Integer = 3270

    On Error GoTo ErrorSetAccessProperty
    ' Explicitly refer to Properties collection.
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    If SetToWhat <> "" Then
        obj.Properties(strName).Value = SetToWhat
        obj.Properties.Refresh
    End If
    SetAccessProperty = True

ExitSetAccessProperty:
    Exit Function

ErrorSetAccessProperty:
    If Err = conPropNotFound Then

' Create property, denote type, and set initial value.
        Set prp = obj.CreateProperty(strName, intType, varSetting)
        ' Append Property object to Properties collection.
        obj.Properties.append prp
        obj.Properties(strName).Value = SetToWhat
        obj.Properties.Refresh
        SetAccessProperty = True
        Resume ExitSetAccessProperty
    Else
        MsgBox Err & ": " & vbCrLf & Err.Description
        SetAccessProperty = False
        Resume ExitSetAccessProperty
    End If
End Function




----- Original Message -----
From: "Lawhon, Alan C Contractor/Morgan Research" <alan.lawhon at us.army.mil>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Thursday, September 11, 2003 1:01 PM
Subject: RE: [AccessD] (Simple?) Problem Creating a "Yes/No" [Data Type]
Field in a MakeTable Query


> John:
>
> Maybe I did something wrong, but I changed the clause/phrase (in SQL view)
> to read as you suggested.  (In QBE "Design View" the expression changed
> to read like this: Record_Select_Flag: False)
>
> After running the [revised] query and creating the table, the resulting
> data type (for the field) is a "Number" data type - NOT a "Yes/No" or
> "True/False" data type.
>
> Close, but no cigar ...  (I'll keep trying, but any other suggestions will
> be greatly appreciated.)
>
>
> Alan C. Lawhon