VBA to convert the data type of a field

[min3010]

Hi,

I have imported a text file into access and then done an update query to change some records that held a '?' character to 0. Then I changed the datatype of that field from Text to Integer (Double). The importing and query part have been added to a macro to automate this

However, I have not found a way to do the conversion of text datatype to integer for the particular field, in my macro. Does anyone know how this can be done via VBA?

So I need the VBA to convert the datatype of a field in a table from text to integer.

Many Thanks.

 

[boblarson]

I don't remember the exact syntax but Googling would find it fairly easily. Look into the ALTER TABLE sql command.

But, you could also do this like this.

1. Create one table where you import into (don't do a new table each time, just empty the data and import to an existing table).

2. Create a query to transform the data, using conversions like CDbl, and put it into your permanent table.

 

[aziz rasul]

ALTER TABLE tblIntExp9 ALTER COLUMN RangingIDTemp NUMBER;
ALTER TABLE tblIntExp9 ALTER COLUMN RangingID LONG;
ALTER TABLE tblIntExp9 ALTER COLUMN RangingID DOUBLE;