1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Copy and paste multiple cells in access 2003

Discussion in 'Business Applications' started by captainphoenix, Feb 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. captainphoenix

    captainphoenix Thread Starter

    Joined:
    Mar 17, 2008
    Messages:
    28
    I have a table that includes a column for "Date of Death". In many cases, the date is either unknown or only partially known. I created another column entitled "Date of Death1". DOD1 is mm/dd/yyyy and DOD is text. I copy and pasted all the data from DOD to DOD1 and DOD1 returned a listing of all of the data it could recognize as dates in proper mm/dd/yyyy format. I'm about to convert DOD1 to text so that I can then fill in the remaining missing data (i.e. "1884"; "12/??/1908"; "?? Mar 198?"; "Apr 1779" etc.). The point is to convert everything to a more uniform, condensed format (because some dates were listed as "February 27th, 1893", and some as "6 Nov 1955" and some as "13/aug/1964" etc.). By filtering I can easily sort out all the dates in DOD that are year only ("yyyy"), however the only way it seems to transfer the remaining data from DOD to DOD1 is to type it in manually as I cannot copy and paste multiple cells by highlighting or using CTRL + or SHIFT +, other than to highlight the entire column. Total of about 8000 records, roughly 1200 more dates to transfer, and roughly a half dozen more columns to repeat this process (Date of Birth, Date of Burial, Date of Purchase, etc.). After that is all said and done I will need a way to sort according to date, given that not all of the information is known and that thousands of entries are going to be year only and hundreds will contain the character "?". Any help?
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Sounds like a nightmare captainpoenix.

    Hows your VBA\vbscript? regulare expressions? If you can do some of that ..extract the dates and a key to the table to a .csv file and commence to fighting in that arena. You could also start a new access DB dedicated to fixing these dates. Your going to need a queries and tables with keys to allow you to update the fixes back to the orginional.

    You need to see if you can separate the battle in segments\subsets of like problems. Then a series of updates specific to the segments will save you a lot of manual keying
    1. In the critera for a select query do a find for "/" - Like "*" & "/" & "*"
    This will get you the subset of dates in that specific format.
    2. the comma - Like "*" & "," & "*"
    This will get you the ones with commas.
    3. Checking the lenth of a field may help group like issues (sometimes within subsets)
    In select query make new field like this L_DOD: Len([Date of Death]) (I hate spaces in field names!)

    As you mess with this you will see some patterns you can fix with update queries using the replace function.
    ie.. Replace([Date of Death],"??","01") or perhaps

    UPDATE MyTable SET MyTable.DOD1 = Replace("DOD1","??","01")
    WHERE (((Len([DOD]))>5));

    After you fix a record(s) flag them as fixed and/or romove them from the 'working file' so they don't get in the way of your next fix.
    This will be a tedious trial and error process but you should be able to use Access to make your life easier. I would think anything over 75% correction rate would be a great success.
     
  3. captainphoenix

    captainphoenix Thread Starter

    Joined:
    Mar 17, 2008
    Messages:
    28
    It sounds like it'd just be easier to import the whole table into VB suite and just do it from there, esp. because I'm not all that familiar with Access's language...or just do a whole ton of manual typing.

    "I would think anything over 75% correction rate would be a great success."

    I'm already at around 75% just from using "find" and "replace all". Is this to say that's really the best I can realistically do from within Access without resorting to manual typing?

    Finally, I'm to assume that means no chance of any selective drag-and-drop in Access?
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I was just guessing on the success rate. I haven't seen the data. But no, Access table/query manual editing isn't, by design, very robust. You can use the criteria in query to find and update with the 'replace' function. You can wrap the replace with IIF logic or make modules with VBA that evaluate/update the data.

    Excel has the manual editing functionality you refer to. But its gets irritating in Excel as it tries to convert every date into integers and display it as a date.

    To programmatically fix the dates VB sounds like an option for you. If this is a 'one off' your find and replace method may work for you. If you're going to continue processing data like this I would want to automate.

    I might use the queries in Access to break out subsets. Then export the tables\queries to a csv file (losing all formatting) then bring into vb...

    But it’s starting to sound like I'm 'over engineering' your project.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As Draceplace says queries can do a lot of what you want, as can VBA code.
    But how do fill in such things as 12/??/1908?
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090932

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice