table has wrong data type

[Eric]

Hello. I am wondering if it's possible to change a fields
data type via VBA? The reason I am asking is I have a make
table query (tmpGloss10Week2 is the table name) that the
field called PCTAcceptable is a text field instead of a
Number data type field. I have tried the following code
with no success:

Sub ChangeDataType()

Dim dbs As DAO.Database
Dim tbl As TableDef
Dim fld As Field

Set dbs = CurrentDb
Set tbl = dbs.TableDefs("tmpGloss10Week2")
Set fld = tbl.Fields("PCTAcceptable")

fld.Attributes = dbFixedField

End Sub

What am I missing?

 

[Andi Mayer]

Hello. I am wondering if it's possible to change a fields
data type via VBA? The reason I am asking is I have a make
table query (tmpGloss10Week2 is the table name) that the
field called PCTAcceptable is a text field instead of a
Number data type field. I have tried the following code
with no success:

Sub ChangeDataType()

Dim dbs As DAO.Database
Dim tbl As TableDef
Dim fld As Field

Set dbs = CurrentDb
Set tbl = dbs.TableDefs("tmpGloss10Week2")
Set fld = tbl.Fields("PCTAcceptable")

fld.Attributes = dbFixedField

End Sub

What am I missing?

look at the SQL help under
ALTER TABLE mytable ALTER COLUMN

 

[Tim Ferguson]

The reason I am asking is I have a make
table query (tmpGloss10Week2 is the table name) that the
field called PCTAcceptable is a text field instead of a
Number data type field

If the tmp prefix means it's temporary table, then you could (a) fix the
code that created it, so that future tables would be correct, and (b) use
a query to cast the text into a number for the time being...

SELECT ALL GlossID, CInt(WeekNo) AS WeekNumber, SomethingElse
FROM tmpGloss10Week2

etc

HTH


Tim F