Hi, im not sure whether this is at all possible but would like some confirmation please.
Is there any way of automatically changing the datatype of a field within a table without going into design view and doing it manually?
The reason for this is that i regulary import data from an Excel file and compare it with information within the database, however one field in the database is set to 'Text' whilst its corresponding field in the excel file is set to 'Number'. In order to compare details (calculations not required) i need to amend the datatype 'Number' to 'Text'.
The information held in this field is a 14 digit number and using datatype 'Number' will not capture the number in the correct format.
i.e. 12345678901234 is displayed as 1.2345678901234E+13.
Any Ideas ?? Im using Access '97
5 1478
Hi, im not sure whether this is at all possible but would like some confirmation please.
Is there any way of automatically changing the datatype of a field within a table without going into design view and doing it manually?
The reason for this is that i regulary import data from an Excel file and compare it with information within the database, however one field in the database is set to 'Text' whilst its corresponding field in the excel file is set to 'Number'. In order to compare details (calculations not required) i need to amend the datatype 'Number' to 'Text'.
The information held in this field is a 14 digit number and using datatype 'Number' will not capture the number in the correct format.
i.e. 12345678901234 is displayed as 1.2345678901234E+13.
Any Ideas ?? Im using Access '97
Hi Jon,
Try the syntax below, but keep in mind that changing a data type to an incompatible data type will result in the loss of data....so if you do this, always make sure your database is backed up. -
CurrentDb.Execute "ALTER TABLE YourTable ALTER COLUMN YourColumn INTEGER"
-
The above code illustrates how to alter yourTable and your column(i.e. field) to an integer. You need to replace yourTable and YourColumn with the actual names of these objects in your database.
.
Cheers puppydogbuddy,
Only one problem, it works when i run it in Access 2003 but not in Access 97, apparently the Microsoft Jet Engine does not support the use of ALTER TABLE.
The help states to use the DAO create methods instead but i cant see how i would alter the column data type.
Its not the end of the world if this cannot be done but it would be annoying. Has anyone got any ideas ?
Cheers puppydogbuddy,
Only one problem, it works when i run it in Access 2003 but not in Access 97, apparently the Microsoft Jet Engine does not support the use of ALTER TABLE.
The help states to use the DAO create methods instead but i cant see how i would alter the column data type.
Its not the end of the world if this cannot be done but it would be annoying. Has anyone got any ideas ?
Jon,
Did not know alterTable not available in Access97. Ok. Here is another way: Instead of changing the data type in the table, wrap the table field in Access's built in conversion functions as needed.
Example: CDbl(Value([YourField]))
Thanks for your help, ive actually decided to take a less complicated approach by appending the imported table into a blank table with the same field headings but with amended datatypes.
Cheers though, am definately going to have to read up on VBA.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: GGG |
last post by:
I have a situation where at tool is passing me a large array of
strings that I need to process in a particular type of data. Each item
in the array gets to me as a pair of std::strings, basically,...
|
by: Shailesh Humbad |
last post by:
I wrote a short page as a quick reference to c++ integer data types.
Any feedback welcome: http://www.somacon.com/blog/page11.php
|
by: theshowmecanuck |
last post by:
As a matter of academic interest only, is there a way to programmatically
list the 'c' data types? I am not looking for detail, just if it is
possible, and what function could be used to...
|
by: ramu |
last post by:
Hi,
I want to call a vc++ function from a c program on linux. So
when I do this dosen't the VC++ datatypes differ with c datatypes.
Because we don't have some vc++ data types in c. I have to...
|
by: mesut demir |
last post by:
Hi All,
When I create fields (in files) I need assign a data type like char,
varchar, money etc.
I have some questions about the data types when you create fields in a
file.
What is the...
|
by: Rick A |
last post by:
The database I'm developing for a local charity has a table with detail
records of individual transactions with our donors. This table has an
integer "donorID" column that is a foreign key to a...
|
by: Arpan |
last post by:
The .NET Framework 2.0 documentation states that
An Object variable always holds a pointer to the data, never the data
itself.
Now w.r.t. the following ASP.NET code snippet, can someone please...
|
by: Joel Hedlund |
last post by:
Hi!
The question of type checking/enforcing has bothered me for a while, and
since this newsgroup has a wealth of competence subscribed to it, I
figured this would be a great way of learning...
|
by: Alex |
last post by:
Hi,
I have three different data types coming out of my SQL tables that I
need to align with my VB code and the data set properties. I'm
getting an error message that reads "SQL exception...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |