ALTER TABLE statement producing Syntax Error

[JenavaS]

I am trying to create a macro to change a data type in a column. The table was created using a "make-table" query, and the column(s) I want to modify were created using a calculation in my query. Therefore, the data in the column has a lot of numbers after the decimal.

I could go into the table's design view and change the decimals from Auto to 2, and the format to Percent, however I would like this to happen automatically after the make-table query runs.

So, I created a macro. So far all I have in the macro is a RunSQL statement that looks like this:

Alter table [1 - Alert Report Export - Dept,Reg,Mo] ALTER COLUMN [Planned Growth] float(10,2)

I get a Syntax Error when I try to run this. Interestingly, if I chnange the data type to Text(50) it works.

What am I doing wrong? Is there a better way to get where I'm going?

Your help is appreciated.

[FishVal]

Hello, JenavaS.

Actually field type (you try to set) is not the same as field property (you should set). You may try the following code to set "DecimalPlaces" property of a field

CurrentDb.TableDefs("YourTableName").Fields("YourFieldName").Properties("DecimalPlaces").Value=4
 

Regards,
Fish

[JenavaS]

I tried this and get the error Property Not Found.

This is my code:

CurrentDb.TableDefs("1 - Alert Report Export - Dept,Reg,Mo").Fields("Planned Growth").Properties("decimal places").Value = 4

[JenavaS]

I also tried this:

CurrentDb.TableDefs("1 - Alert Report Export - Dept,Reg,Mo").Fields("Planned Growth").Properties("decimalplaces").Value = 4

And get a Property not found error.

[JenavaS]

You got me pointed in the right direction, but this acticle explains more in depth what has to be done to acheive this:

http://visualbasic.about.com/od/usevb6/l/aa101602a.htm

Essentially one must first add the property to the collection, then update it.