Results 1 to 12 of 12
  1. #1
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104

    Change Field Data Type


    Hi all, hours on Google, to no avail.
    I am trying to alter the datatype in a field from Number to Short Text. The field never contains more than 18 characters, and they are always numeric. However for onward transmission this field must be a text field.
    This is the code that I thought should work, but it doesn't:
    Code:
    DoCmd.RunSQL "ALTER TABLE TblEpisode ALTER COLUMN EpisodeID Text(20);"
    Can anyone correct me or point to an infallible alternative?
    thanks in anticipation
    Pete

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Is the field episodeID a primary key or is it linked to another field in a relationship or query.

    When you copy and paste that code into a query window in SQL mode, does it work?


    Sent from my iPhone using Tapatalk

  3. #3
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104
    Hi Andy, no, no and no! I just want to change the data type on the fly!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Does it give you an error? Is the table in use, on a form or ??

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Where, exactly, are you trying to do this? In v2007, using your code behind a Command Button, it performs exactly as you want it to, for me!

    Linq ;0)>

    Just had a thought (at my age, I try to hold onto to them, they happen so seldom) since the Datatype name has been changed from Text to ShortText, in later versions, does the SQL need to us ShortText, rather than Text? I can't test this in v2007.
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Shouldn't you be able to do this in data-sheet/design view? Just change the field type.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The OP said
    Quote Originally Posted by DubCap01 View Post

    ...I just want to change the data type on the fly...
    He's apparently exporting it, needs to change it before doing so, and probably (I'm guessing, here) is going to change it back to a Number after the export.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by Missinglinq View Post
    Where, exactly, are you trying to do this? In v2007, using your code behind a Command Button, it performs exactly as you want it to, for me!

    Linq ;0)>

    Just had a thought (at my age, I try to hold onto to them, they happen so seldom) since the Datatype name has been changed from Text to ShortText, in later versions, does the SQL need to us ShortText, rather than Text? I can't test this in v2007.

    I'm going to give this a shot in v2007.

  9. #9
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    The code you posted is working on 2007 and 2016. Attached is a proof-of-concept for converting back and forth.

    DataTypeConversion.zip

    You will get an error in the even of a null value, though.

    There is a work-around if this isn't working in your database. You can create code to copy the data to a dummy table with specified data types, drop the current table, then rename your dummy table to match your original table. That would allow you to do whatever you need on-the-fly.

  10. #10
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104
    Hi all, thanks for the replies. I don't want to change it in design view, as it interrupts the automation of importing and exporting somewhat!
    'Text(20)' to ShortText(20) doesn't work either, so maybe I will try another method. Thanks for all your help folks
    I will leave the thread open for now, just in case someone else has other suggestions
    Pete

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In TblEpisode, I would add another column that is Text (or Short text - I'm using A2010), maybe named "EpisodeTxt".
    Then execute an update query to convert the number ("EpisodeID") to text ("EpisodeTxt").
    Instead of exporting "EpisodeID", export "EpisodeTxt".


    IMHO, Constantly changing a field's data type is a good way to introduce corruption.

  12. #12
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by DubCap01 View Post
    Hi all, thanks for the replies. I don't want to change it in design view, as it interrupts the automation of importing and exporting somewhat!
    'Text(20)' to ShortText(20) doesn't work either, so maybe I will try another method. Thanks for all your help folks
    I will leave the thread open for now, just in case someone else has other suggestions
    Pete
    The file I uploaded works. It uses a button on a form to change the data type.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA To Change Field Type
    By jo15765 in forum Programming
    Replies: 7
    Last Post: 12-16-2016, 06:46 PM
  2. How Do I Change Data Type
    By LeadTechIG in forum Database Design
    Replies: 9
    Last Post: 01-07-2015, 03:05 PM
  3. While Importing Change in data type
    By drunkenneo in forum Import/Export Data
    Replies: 1
    Last Post: 09-01-2014, 06:49 AM
  4. Change Percentage Data Type
    By jo8701 in forum Access
    Replies: 1
    Last Post: 02-08-2012, 07:15 AM
  5. Replies: 5
    Last Post: 01-09-2012, 05:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums