change field Data Type

[tdubs]

Hi,

is it possible to change the data type a field in a table using VBA code?

I know how to retrieve it but don't know how to set it...

I retrieve it by

temp = CurrentDb.TableDefs.Item("CMAssign").Fields.Item("Chassis").Type

i got temp = 19.. so to set it up i tried using

CurrentDb.TableDefs.Item("CMAssign").Fields.Item("Chassis").Type = 19

but doesnt work.. it says invalid operation.

any idea? Thanks in Advance for your help, I really Appreciate it

 

[deBassMan]

Hi

may I respectbly ask why you want to do this??

cheers

 

[tdubs]

Hi

may I respectbly ask why you want to do this??

cheers

Becaue I am importing an excel table to access, where all the columns are transfered as text, even the number ones...now I need to convert a couple of specific fields to number type...

this is due to the sorting problem... I like to keep all my column as text to avoid the type dismatch when using SQL... but now some columns need to be sorted and if they are stored in text while they are actually numbers, then the sorting would not work properly..

unless there is a way to sort numbers as text and in a proper way..

 

[deBassMan]

Hi

If you use Get External Data->Import it will bring Excel data in number format

good luck

 

[tdubs]

Hi

If you use Get External Data->Import it will bring Excel data in number format

good luck

yeah iknow.. just that I need to do it in VBA code... thanks anyways..

 

[tdubs]

I am trying to work it out in another way..

ColumnName would be the column I want to change

- I add a column named TempColumn
- Copy data from ColumnName to TempColumn
- Delete ColumnName
- change TempColumn's name to ColumnName

The problem is that I can't delete ColumnName... it gives me the error of 'cannot delete field part of an index or needed by the system'

I tried with SQL command and also:

CurrentDb.TableDefs.Item(DBName).Fields.Delete "ColumnName"

but same.. I can't seem to delete any of the columns on my table... btw..
the table was imported from a excel file using transferspreadsheet

I tried searching the forum for similar treads.. I found one were they told the guy to search the forum as the solution was already posted.. but tried searching a lot with different words .. but couldn't find it...

Anyone can help me out??? Thanks.. really need the help.. need to get this done by tonite..><"

 

[tdubs]

Oh, figure out myself.... just needed to get rid of all the indexes
thanks anyways