Chaging DataType With Module

[Irish_Griffin]

Hey Guys,
New here and hoping I found a place to stay to help and get help for VBA.

Heres my question:

I have a table that imports with a column that is a text field. To use the table the data type of this column needs to be "number".

What I have found is I can use ALTER TABLE or DOA.... but this has only left me scratching my head.... Please help

 

[Irish_Griffin]

Function Convert_Text()


Dim db As DAO.Database
Dim tbldef As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbldef = db.TableDefs("Buyers_Guide_and_Part_Master")

Set fld = tbldef.Fields("VNDR_CODE")
fld.Type = dbInteger
tbldef.Fields.Refresh

Set db = Nothing
Set fld = Nothing

End Function


not working

 

[CraigDolphin]

As an alternative, you could leave the field as a text field and, when you need to use it as a number, use the CInt() function to convert it to an integer at the time of use. Not what you're looking for perhaps, but it might be a workaround if no one else provides you with the answer you want.

 

[Irish_Griffin]

As an alternative, you could leave the field as a text field and, when you need to use it as a number, use the CInt() function to convert it to an integer at the time of use. Not what you're looking for perhaps, but it might be a workaround if no one else provides you with the answer you want.

This could do it with an extra step..... but I'm looking for some code that can replace going into the table design and change the field data type from text to number.....

There has got to be some simple code to do this.... Everything I found online hasn't worked.

Thanks for the response. I use to live in Bellingham, I love it there, very nice place. Then I moved to Michigan for school.....Blllaaaaaaaaaaaa I hate it here.

 

[DJkarl]

This could do it with an extra step..... but I'm looking for some code that can replace going into the table design and change the field data type from text to number.....

There has got to be some simple code to do this.... Everything I found online hasn't worked.

Thanks for the response. I use to live in Bellingham, I love it there, very nice place. Then I moved to Michigan for school.....Blllaaaaaaaaaaaa I hate it here.

Simplest code I've found

CurrentDb.Execute "ALTER TABLE [YourTableName] ALTER COLUMN [FieldName] NewDataTypeHere"

You can search for allowed data types, example Text(10) would set the field to a text field that allows 10 characters, DATE would set the field to a Date/Time field and so on.

 

[Irish_Griffin]

WOOT!!!




Sub Change_DataType()

Dim dbs As Database

Set dbs = CurrentDb

dbs.Execute "ALTER TABLE Buyers_Guide_and_Part_Master " _
& "ALTER COLUMN VNDR_CODE number;"

dbs.Close

End Sub


THANK YOU!!! I hate getting blocked with code I can't figure out!!! Exspecialy something that should be (and was) simple.

-Aaron