Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: VBA code to copy data from one column to another column based on criteria

    Hello, this is my first post on this forum. I don't have a VBA coding background so I don't even know where to start as far as coding this myself. I would be very appreciative if you guys could help me out. Here are the details of what I would like to do:

    In my DB I have a table with 8 fields. The two in question are the AR Tech field which is completely blank and the Bill Number field which sits to the right of the AR Tech field. Because of how the data was assembled on the txt file that I imported into Access, the AR Tech name sits in the Bill Number column and all of their transactions are shown underneath their name in the records below it. I want to have Access copy the name AR Tech name from the Bill Number field and then paste it in the AR Tech column to the left of all their bill numbers. Every bill number starts with the numbers 673. I'm thinking the code could tell Access to copy that name to each record that returns a bill number that starts with 673. When access gets to the next AR Clerks name in the Bill Number column it will stop, copy it and then paste that name in the AR Tech column until it hits the next name up until the end. This is complicated for me but i'm sure a simple request for you guys. Again, I would appreciate any help you could give me. Below is an example of how the data is currently shown in the database. Billing Num is one column and AR Tech is another.


    Billing Number AR Tech

    John Wilson
    673038494
    673940440
    Jane Brown
    673393049
    673004444

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    This should be easily done with an UPDATE query. I'm kinda out of time here, but I am sure one of the SQL wizards around here will throw some SQL together for you to try.

    Take a backup first!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Thanks, I was thinking the same thing about the update query. However, I tried an update query earlier today and couldn't figure out how to get the names to stop copying in the AR Tech column when the Bill Number field gets to the next AR Tech's name. My update query just copied the first AR Tech's name down the whole AR Tech column.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I suck at writing SQL beyond the basics, but I can build almost any SQL with the help of the query designer and some use of copy and paste. I refuse to learn SQL in Jet; when I do learn SQL it will be "proper" SQL and I know that Jet is different to 'normal' SQL... in various ways.

    However...

    You would need something that is like this:

    UPDATE [YourTableName] SET [AR Tech] = [Billing Number]
    WHERE Left([Billing Number],3) <> "673";

    Of course you would need to translate that wherever appropriate.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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