Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Copy all data in one column to another in the same table Using VBA

    I have a table (costsettest) for ingredients & cost ID, Ing_Code, Description, 97, once a month we update the costs but need to keep the old costs to reference back to.

    I have figured out how to insert a new column via VBA that increments the number so now I have a column 97 & 98, 99..... but I also need the values to be copyed into the 98 column.

    CurrentDb.Execute ("ALTER TABLE Costsettest ADD " & strField & " Number;")

    Is there a way to also copy the values from the 97 column while creating the new column (field) or after the column(field) is created a simple way to copy & paste all the values from column 97 to column 98, using VBA.

    I can open up the table select the entire column 97 click copy & then paste this into column 98 but cannot see how to do this via VBA.

    Please help not done much programing before & Im going mad here.
    Attached Thumbnails Attached Thumbnails Access Help.JPG  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use
    Code:
    UPDATE Costsettest SET <NewColumn> = <OldColumn>
    Then
    Code:
    UPDATE Costsettest SET <OldColumn> = Null
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    6
    Thanks for your Help this works

    UPDATE CostSettest SET CostSettest.[98] = [97];

    Now this works is there a way to set the [98] & [97] from a global variable, I have a global variable called =selected_Cost_Set() & =selected_Cost_Set2()that has a number value in it i.e. 98 which will increment every month, is there a way to add this variable into the SQL Query.

    Many Thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    CurrentDb.Execute ("UPDATE CostSettest SET CostSettest.[" & GlobalVariable & "] = [97];"
    Have a nice day!

Tags for this Thread

Posting Permissions

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