Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Field Data Types (Access 2003)

    I have a table that has a text field. The data in the table come from an Oracle database where the field is a mix of alpha and numeric data. I strip off the alpha characters, delete leading/trailing spaces and generally clean up the information as all I need are the numbers. After I finish scrubbing the data, I want to change the field to be numeric for many reasons, such as calculating averages and having information sort correctly (11 is not less than 2). I know I could copy the data to another table with that field formatted as numeric. However, because there are hundreds of thousands of records, I do not want to balloon the database by having two tables even if one would ultimately be deleted.

    Is there a way to programmatically change the data type of the field from text to number? If I open the table in design view and change the data type, it won't save the change due to the overall size of the table.

    I could handle the text field in a separate table and update the clean data to the master table, if necessary. If I could change the field programmatically, I would rather go that route (fewer steps and a smaller database).

    Is there a way?

    Thanks!

    Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Field Data Types (Access 2003)

    In theory, yes. But if you cannot change the field interactively, I don't know if you will succeed programmatically.
    The instruction needed to change the data type of a field is like this:

    CurrentDb.Execute "ALTER TABLE [NameOfTable] ALTER COLUMN [NameOfField] INTEGER", dbFailOnError

    You must, of course, substitute the correct names. This instruction will attempt to change the data type to Long Integer. If your field contains numbers with decimal places, use FLOAT instead of INTEGER.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Field Data Types (Access 2003)

    It worked perfectly!

    Thanks, Hans.

    I appreciate your help.

    Nancy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •