Change Data Type from Integer to Boolean

[Guest]

Dear All,

If I am trying to change the datatype of a table field from an Integer to a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object at
runtime?

Thanks again.

Alastair MacFarlane

 

[Brendan Reynolds]

From the help file on the Type property ...

<quote>
For a Field or Property object, this property is read/write until the object
is appended to a collection or to another object, after which it's read-only
</quote>

You can change the data type this way instead ...

Public Function TestIntToBool()

Dim strSQL As String
strSQL = "ALTER TABLE tblTest ALTER COLUMN TestInt BIT"
CurrentDb.Execute strSQL

End Function

 

[Allen Browne]

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

in message
news:[email protected]...

 

[Guest]

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

in message

Dear All,

If I am trying to change the datatype of a table field from an Integer to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object at
runtime?

Thanks again.

Alastair MacFarlane

 

[Brendan Reynolds]

In your other thread, you seemed to want a check box, and I replied with an
example of how to do that. Setting the Format property has, as far as I
know, no effect on a check box.

--
Brendan Reynolds

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

"Alastair MacFarlane" <[email protected]>
wrote
in message

Dear All,

If I am trying to change the datatype of a table field from an Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object
at
runtime?

Thanks again.

Alastair MacFarlane

 

[Brendan Reynolds]

If you do want to set the format property, it's one of those extended
properties that doesn't exist until the first time a value is assigned to
it. The following code assumes that the property has not been previously
created, which will be true if this is a newly created field. As I said
earlier, though, this won't have any effect on a check box, only text boxes
and (I think - I haven't tested this) combo boxes ...

Public Sub TestSetFormat()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")
Set fld = tdf.Fields("TestInt2")
Set prp = fld.CreateProperty("Format", dbText, "Yes/No")
fld.Properties.Append prp

End Sub

--
Brendan Reynolds

In your other thread, you seemed to want a check box, and I replied with
an example of how to do that. Setting the Format property has, as far as I
know, no effect on a check box.

--
Brendan Reynolds

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

"Alastair MacFarlane" <[email protected]>
wrote
in message
Dear All,

If I am trying to change the datatype of a table field from an Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object
at
runtime?

Thanks again.

Alastair MacFarlane

 

[Allen Browne]

The Format property cannot be set with DDL, so it's back to DAO to set that.

Some of these properties don't exist until you create them, so here's what I
actually use to do this kind of thing. You could call it as:
Call SetPropertyDAO(dbEngine(0)(0).TableDefs("tblDailyNonAttendees
").Fields("Contact"), dbText, "True/False")

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.

in message

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"


"Alastair MacFarlane" <[email protected]>
wrote
in message

Dear All,

If I am trying to change the datatype of a table field from an Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object
at
runtime?

Thanks again.

Alastair MacFarlane

 

[Guest]

Brendan,

Sorry to be dense but there seems to be a difference between manually
setting the field to Yes/No and programatically setting the field to Yes/No.
In the former, i.e. manually, you see a check box and when you use the ALTER
COLUMN command you get a numerical 0.

I maybe should've rephrased the question. I had presumed that changing the
format would give me the desired result.

Public Function TestIntToBool()
Dim strSQL As String
strSQL = "ALTER TABLE tblDailyNonAttendees ALTER COLUMN Contact BIT"
CurrentDb.Execute strSQL
End Function

Thanks again for your support.

Alastair MacFarlane

In your other thread, you seemed to want a check box, and I replied with an
example of how to do that. Setting the Format property has, as far as I
know, no effect on a check box.

--
Brendan Reynolds

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

"Alastair MacFarlane" <[email protected]>
wrote
in message
Dear All,

If I am trying to change the datatype of a table field from an Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object
at
runtime?

Thanks again.

Alastair MacFarlane

 

[Brendan Reynolds]

Microsoft use of the term "Yes/No" to describe both a data type and a
format. This dual use of the term can get a little confusing at times.

You're correct that when you create a Boolean ("Yes/No") field via the UI,
it displays by default as a check box. When you create it programmatically,
it displays as a text box containing either 0 (False) or -1 (True).

To have the programmatically created field displayed in the table as a check
box, you need to change the DisplayControl property - I posted an example in
the other thread. Changing the Format property to "Yes/No" would cause the
field to still be displayed as a text box, but it would display False and
True instead of 0 and -1.

The field doesn't have to be displayed as a check box in order to bind check
boxes on a form to the field. It just makes life a little easier when using
form wizards or dragging and dropping from the field list.

--
Brendan Reynolds

Brendan,

Sorry to be dense but there seems to be a difference between manually
setting the field to Yes/No and programatically setting the field to
Yes/No.
In the former, i.e. manually, you see a check box and when you use the
ALTER
COLUMN command you get a numerical 0.

I maybe should've rephrased the question. I had presumed that changing the
format would give me the desired result.

Public Function TestIntToBool()
Dim strSQL As String
strSQL = "ALTER TABLE tblDailyNonAttendees ALTER COLUMN Contact BIT"
CurrentDb.Execute strSQL
End Function

Thanks again for your support.

Alastair MacFarlane

In your other thread, you seemed to want a check box, and I replied with
an
example of how to do that. Setting the Format property has, as far as I
know, no effect on a check box.

--
Brendan Reynolds


"Alastair MacFarlane" <[email protected]>
wrote
in message news:[email protected]...

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a
Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

:

Last time I tried, I was also unable to change the data type of a
column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"

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

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

"Alastair MacFarlane" <[email protected]>
wrote
in message
Dear All,

If I am trying to change the datatype of a table field from an
Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field
object
at
runtime?

Thanks again.

Alastair MacFarlane

 

[Guest]

Thanks Allen and Brendon for the lengthy comments and code posted. I
appreciate your time and I now understand your comments and sample code.

Thanks again.

Alastair

The Format property cannot be set with DDL, so it's back to DAO to set that.

Some of these properties don't exist until you create them, so here's what I
actually use to do this kind of thing. You could call it as:
Call SetPropertyDAO(dbEngine(0)(0).TableDefs("tblDailyNonAttendees
").Fields("Contact"), dbText, "True/False")

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.

in message

Thanks for the reply Allen and Brendan,

I am using the following and it does change the Contact Field to a Yes/No
Data Type and I was wondering if there was a way to specify that the
format
was set to TRUE/FALSE for the Yes/No field?

DBEngine(0)(0).Execute "ALTER TABLE tblDailyNonAttendees ALTER COLUMN
Contact YESNO;"

Thanks again.

Alastair MacFarlane

Last time I tried, I was also unable to change the data type of a column
using DAO.

In JET 4 (Access 2000 and later), you can do it with DDL. Try:

dbEngine(0)(0).Execute "ALTER TABLE Table1 ALTER COLUMN [Type] YESNO;"


"Alastair MacFarlane" <[email protected]>
wrote
in message
Dear All,

If I am trying to change the datatype of a table field from an Integer
to
a
Boolean (TRUE/FALSE) why does the code below not work?

Private Sub ChangeDataType()
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fd As DAO.Field
Dim pp As DAO.Property
Set db = CurrentDb()
Set tb = db.TableDefs("Table1")
Set fd = tb.Fields("Field1")
Set pp = fd.Properties("Type")
pp.Value = dbBoolean
End Sub

I get the error on the line: pp = dbBoolean

?err.Number
3219
?err.Description
Invalid operation.
?err.Source
DAO.Field

Can anyone advise me how I can change the data type of a field object
at
runtime?

Thanks again.

Alastair MacFarlane