+ Reply to Thread
Results 1 to 6 of 6

Copy and paste linked table structure only, then change data field type for a column

  1. #1
    Registered User
    Join Date
    11-22-2016
    Location
    New York, NY
    MS-Off Ver
    2016
    Posts
    25

    Copy and paste linked table structure only, then change data field type for a column

    Hi all,

    I need to change the data type for a field in order to make some outer joins work, but the field in question comes from a linked table. There is too much data to change the data type in the actual table that is linked so I need a workaround.

    So far I do it like this:
    • Copy and paste linked table as structure only
    • Change the field data type in the new table
    • Append from the linked table

    This way my queries that have outer joins will work. I don't want to do this manually though so is there a way to put this into a macro somehow? Or maybe a better workaround than what I'm doing here?

    Thanks!

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2,792

    Re: Copy and paste linked table structure only, then change data field type for a column

    I don't know what the data type in question, but have you tried using cint or clng (or cstr) functions to make your joins?

    for example if the table a field is a string and table b integer:

    ON tablea.field1 = cstr(tableb.field1)
    Last edited by Arkadi; 01-27-2017 at 03:47 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    11-22-2016
    Location
    New York, NY
    MS-Off Ver
    2016
    Posts
    25

    Re: Copy and paste linked table structure only, then change data field type for a column

    I need to convert a text to a number, so would that formula work in my case?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2,792

    Re: Copy and paste linked table structure only, then change data field type for a column

    Cstr is for conversion into a string... Clng to convert to long integer

  5. #5
    Spammer
    Join Date
    01-26-2017
    Location
    Ywipetovi
    MS-Off Ver
    Ulyrux
    Posts
    1

    Copy and paste linked table structure only then change data field type for a c

    Hi there - the issue is not the structure of the query, but the data. It seems to me that I cannot edit/update the data when accessed through a query. Or perhaps I am just doing something wrong?
    http://stol-v-dom.weebly.com

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2,792

    Re: Copy and paste linked table structure only, then change data field type for a column

    You said it needed to be converted for the join.... But now it sounds like maybe it is when you are passing data to the query? What is the code with the isue?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1