table has wrong data type

  • Thread starter Thread starter Eric
  • Start date Start date
E

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?
 
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?
Click to expand...

look at the SQL help under
ALTER TABLE mytable ALTER COLUMN
 
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
Click to expand...

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
 
Back
Top