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

Amending data types

P: 34
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
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
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

P: 34
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
Expert 100+
P: 1,923
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
Expert 100+
P: 1,923
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

P: 34
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

Post your reply

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