Updating a Field in a table from Text to Long Integer

[ThomMenzies]

I am having trouble changing a field in a table from text to long integer. So far I have:

dim DB As Database
Dim tdf As TableDef
Dim fld As Field

Set DB = CurrentDb()
Set tdf = DB.TableDefs("tbl_VisitData&quot
Set fld = tdf.Fields("Org ID&quot

I can't find a way to update the fld. Any ideas?

 

[CaptainD]

YOu probably have Aplpha Charaters in the field (Alpha Characters are "ABC, #,' etc)If there are alpha then you will need to change them to all numeric first then you should be able to bring up the table in design view and change it from Text to Integer. Word of caution though, [Org ID] could be the link to the data in another table and you'll lose the link if you change the characters only in that one table and not the others.

 

[evalesthy]

I used this recently and it works fine for me.

Sub AlterFieldType(TblName As String, FieldName As String, NewDataType As String)
'The AlterFieldType sub procedure requires three string parameters. The first
'string specifies the name of the table containing the field to be changed. The
'second string specifies the name of the field to be changed. The third string
'specifies the new data type for the field
Dim db As Database
Dim qdf As QueryDef

Set db = DBEngine.OpenDatabase("C:\NComm\NSubscriber_be.mdb&quot

'create querydef object
Set qdf = db.CreateQueryDef("", "SELECT * FROM [Batch Entry Pot]&quot

'add a temporary field to the table
qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN AlterTempField " & NewDataType
qdf.Execute

'copy the data from old field into the new field
qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET AlterTempField = [" & FieldName & "]"
qdf.Execute

'delete the old field
qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN [" & FieldName & "]"
qdf.Execute

'rename the temporary field to the old field's name
db.TableDefs("[" & TblName & "]&quot.Fields("AlterTempField&quot.Name = FieldName

End Sub

then in run following where appropriate

'*****************************************************************
'**Change field properties in existing database table** '*****************************************************************

'set SubscriberName field to txt(20) Batch Entry Pot
'call sub
AlterFieldType "Batch Entry Pot", "SubscriberName", "TEXT(20)"