By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,657 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,657 IT Pros & Developers. It's quick & easy.

ALTER TABLE statement producing Syntax Error

P: 5
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.
Jan 23 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.TableDefs("YourTableName").Fields("YourFieldName").Properties("DecimalPlaces").Value=4
  2.  

Regards,
Fish
Jan 23 '08 #2

P: 5
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
Jan 23 '08 #3

P: 5
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.
Jan 23 '08 #4

P: 5
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.
Jan 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.