473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Amending data types

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
Jun 14 '07 #1
5 1478
puppydogbuddy
1,923 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "ALTER TABLE YourTable ALTER COLUMN YourColumn INTEGER" 
  2.  
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.

.
Jun 15 '07 #2
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 ?
Jun 15 '07 #3
puppydogbuddy
1,923 Expert 1GB
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]))
Jun 15 '07 #4
puppydogbuddy
1,923 Expert 1GB
Jon,
Here you go! The following link is for Access97 and contains a function called AlterFieldType that will do what you want:

http://support.microsoft.com/kb/128016#top
Jun 15 '07 #5
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.
Jun 19 '07 #6

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

Similar topics

3
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,...
13
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
11
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...
8
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...
11
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...
1
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...
7
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...
18
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...
1
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.