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.
4 3558 FishVal 2,653
Recognized Expert Specialist
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 "DecimalPla ces" property of a field -
CurrentDb.TableDefs("YourTableName").Fields("YourFieldName").Properties("DecimalPlaces").Value=4
-
Regards,
Fish
I tried this and get the error Property Not Found.
This is my code:
CurrentDb.Table Defs("1 - Alert Report Export - Dept,Reg,Mo").F ields("Planned Growth").Proper ties("decimal places").Value = 4
I also tried this:
CurrentDb.Table Defs("1 - Alert Report Export - Dept,Reg,Mo").F ields("Planned Growth").Proper ties("decimalpl aces").Value = 4
And get a Property not found error.
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.abo ut.com/od/usevb6/l/aa101602a.htm
Essentially one must first add the property to the collection, then update it.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Adam Fortuno |
last post by:
Was in the midst of doing something today and I attempted to drop a
number of columns in a table with the following:
ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4;
Unfortunately MySQL gave me an error reading:
ERROR 1064: You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'col_2, col_3, col_4' at line 1
|
by: Jane |
last post by:
Does any one know why this statement is failing?
db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "this->qunqtbssc.op() !=
NULLP".) ...
|
by: Mike L |
last post by:
Hello all
I'm trying to migrate a BE from Access to SQL Server.
I've been making changes to the Access BE from the FE with SQL
statements, and want to do the same with the BE in SQL Server.
When running the statement "ALTER TABLE tblTest DROP COLUMN
TestColumn3" from the FE with a SQL Server BE, it works OK.
Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
|
by: Jens |
last post by:
Hi,
Does anyone know if it is possible to alter an access table through ODBC. I
am using the following SQL statement to add a foreign key to a table.
Access returns an error like "Syntax error in CONSTRAINT clause". Some how
access does not like "ON DELETE CASCADE"
any help is much appreciated
ALTER TABLE TData
|
by: Jack |
last post by:
Hi,
I am gathering the input values to a form using Request.form method from the
processing page. After all the data is captured, I am building sql statement
out of it. Using a response.write statement, I am generating the output of
the sql statement which I can ran against the table to insert the row.
However, when I am trying to programmatically use the sql statement for the
insert, I am having the following error:
Error Type:...
| |
by: vasilip |
last post by:
I'm testing out db2 for a project I'm starting that requires proper xml
support and I can't seem to get both xml and spatial data to work well
in the same table.
Once having created a table containing both xml and spatial data fields
I can't seem to find a way to alter the table
I have created a table containing an id, xmldata field and a ST_Point
If I try to drop the xml field with ALTER TABLE TEST DROP COLUMN
|
by: ymk |
last post by:
Simple SQL question.
I am trying to add multiple columns to a temp table and the alter statement throws the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
The alter statement looks like this.
ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)
|
by: Deano |
last post by:
Hi,
I just want to add a text column to a table in my code. I can't find a
simple example of this.
dbs.Execute ("ALTER tblCCOccurrences ADD COLUMN Year1 Text;").
This gives me;
Syntax error in ALTER TABLE statement. 3293
|
by: cmartin1986 |
last post by:
Hi,
I am trying to write code to change my table data types. what i have so far is:
currentdb.execute "alter table getdates alter column Line Unit 0001 date"
If I leave it at that it works fine but when i add identical code for Line Unit 0002 below it I get an error. "syntax error in alter table statement" does anyone know what the problem is or how I can make my code change the data type in all 13 field I need it for?
Thanks...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |