Yes/No field in MAKE TABLE

[Guest]

I am using DoCmd.RunSQL "CREATE TABLE..." to create a table and I need to
define a Yes/No field.

Can someone pass on the syntax?

Much thanks!
Clint

 

[RoyVidar]

I am using DoCmd.RunSQL "CREATE TABLE..." to create a table and I
need to define a Yes/No field.

Can someone pass on the syntax?

Much thanks!
Clint

Something like this?

CREATE TABLE MyTable (
id COUNTER PRIMARY KEY,
MyYesNo YESNO)

Check out this link (watch for linebreaks)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Some tricks need ADO/OLEDB to execute, for instance

Currentproject.Connection.Execute _
"CREATE TABLE MyTable (" & _
"id INT IDENTITY PRIMARY KEY, " & _
"MyYesNo BIT NOT NULL DEFAULT 0)"

Though, if you need it to display as checbox, then that's something
you'll need to program (DAO).

 

[Allen Browne]

the data type is YESNO

For your reference, thse DDL names are compared to the DAO and ADOX names
here:
http://allenbrowne.com/ser-49.html

 

[Guest]

Thanks! I actually used that at one point in my testing but noticed that it
did not create a checkbox in that field when I looked at the table in view. I
just thought it didn't work.

I tried again and checked the table in design mode and see that the field is
marked as Yes/No as the Data Type, but the option down in the Format field is
not filled in.

Here is my code, for reference:

DoCmd.RunSQL "CREATE TABLE " & Tbl3 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [Delete] YESNO);"

Is there a programatic way to set the Format to "Yes/No"?

Thanks for the resource too. I will need to use that several times in the
upcoming weeks.

 

[Allen Browne]

No, you can't set properties like Format or DisplayControl when you use a
DDL query to create your tables. You can't even set the AllowZeroLength
property on your text field, so it is not really practical to create tables
with DDL in Access.

You must create the table with DAO to set the DisplayControl. This involves
CreateTableDef, CreateField, CreateField, and CreateProperty, remembering to
Append each one to the relevant collection.

If these collections are new, there's a basic example of looping through the
Fields of a TableDef here:
http://www.allenbrowne.com/func-06.html

The DisplayControl property is an integer (short), so the code below
demonstrates how to create the property if it does not exist, or set it if
it does:

? Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))

Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! I actually used that at one point in my testing but noticed that
it
did not create a checkbox in that field when I looked at the table in
view. I
just thought it didn't work.

I tried again and checked the table in design mode and see that the field
is
marked as Yes/No as the Data Type, but the option down in the Format field
is
not filled in.

Here is my code, for reference:

DoCmd.RunSQL "CREATE TABLE " & Tbl3 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [Delete] YESNO);"

Is there a programatic way to set the Format to "Yes/No"?

Thanks for the resource too. I will need to use that several times in the
upcoming weeks.

the data type is YESNO

For your reference, thse DDL names are compared to the DAO and ADOX names
here:
http://allenbrowne.com/ser-49.html

 

[Guest]

Thanks! I thought this was going to be the case.

Exactly what I needed.

No, you can't set properties like Format or DisplayControl when you use a
DDL query to create your tables. You can't even set the AllowZeroLength
property on your text field, so it is not really practical to create tables
with DDL in Access.

You must create the table with DAO to set the DisplayControl. This involves
CreateTableDef, CreateField, CreateField, and CreateProperty, remembering to
Append each one to the relevant collection.

If these collections are new, there's a basic example of looping through the
Fields of a TableDef here:
http://www.allenbrowne.com/func-06.html

The DisplayControl property is an integer (short), so the code below
demonstrates how to create the property if it does not exist, or set it if
it does:

? Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))

Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks! I actually used that at one point in my testing but noticed that
it
did not create a checkbox in that field when I looked at the table in
view. I
just thought it didn't work.

I tried again and checked the table in design mode and see that the field
is
marked as Yes/No as the Data Type, but the option down in the Format field
is
not filled in.

Here is my code, for reference:

DoCmd.RunSQL "CREATE TABLE " & Tbl3 & "([UserGroup] TEXT(20), [EID]
TEXT(15), [LastName] TEXT(75), [FirstName] TEXT(75), [Delete] YESNO);"

Is there a programatic way to set the Format to "Yes/No"?

Thanks for the resource too. I will need to use that several times in the
upcoming weeks.

the data type is YESNO

For your reference, thse DDL names are compared to the DAO and ADOX names
here:
http://allenbrowne.com/ser-49.html

I am using DoCmd.RunSQL "CREATE TABLE..." to create a table and I need
to
define a Yes/No field.

Can someone pass on the syntax?

Much thanks!
Clint