Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting single comma-separated row into multiple rows (Access 2010) again

    Please help. I posted a question over a year ago (listed below) and I am having a issue getting the response resolution provided to work.

    I am having a problem when attempting to run the code. I receive the following error:

    Run-time error ' - 2147352565 (8002000b)':
    The item with the specified name wasn't found.

    After clicking Debug, it highlights:
    Sheets(zDestSht).Cells(1, 1).Value = "Company#"

    Would you know how to resolve this problem?

    Thanks!

    Converting single comma-separated row into multiple rows (Access 2010)

    If anyone could provide advice on how to solve my dilemma, that would be terrific.

    I have a table in Access 2010 that contains a comma delimited list of zip codes in a single cell by company#. This list is provided to my database from a different system and the list is unable to be modified. I would like to create a new table with the list where the single comma separated rows are converted into multiple rows with 1 zip code for each company#.

    Attached is this post is a sample of the data I am using. The table is called tblListingsAndZips.

    I would like to keep my original table and create a new table with a single zip code listed for each company#.

    Any help would be much appreciated!



    kgudgel,

    If this is what you're after:

    Then this code:
    Code:
    Option Explicit

    Sub ProcessZips()

    Dim vRawZips As Variant
    Dim iZipCnt As Integer
    Dim iCntr As Integer
    Dim lDestRow As Integer
    Dim zCCode As String
    Dim zDestSht As String

    Application.ScreenUpdating = False
    [C2].Select
    lDestRow = 2
    zDestSht = "NewList"
    Sheets(zDestSht).Cells(1, 1).Value = "Company#"
    Sheets(zDestSht).Cells(1, 2).Value = "ZipCode"

    Do
    ActiveCell.Offset(1, 0).Select

    vRawZips = Split(ActiveCell.Offset(0, -1).Value, ",")
    zCCode = ActiveCell.Value
    iZipCnt = UBound(vRawZips) - 1 '*** Array zero based!

    For iCntr = 0 To iZipCnt
    Sheets(zDestSht).Cells(lDestRow, 1).Value = zCCode
    Sheets(zDestSht).Cells(lDestRow, 2).Value = vRawZips(iCntr)
    lDestRow = lDestRow + 1
    Next iCntr

    Loop Until ActiveCell.Value = ""

    End Sub 'Process Zips

    Will accomplish the task in Excel and you can then import the table into Access. HTH
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    You probably don't have a sheet called "NewList". Do you?

    cheers, Paul

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I suspect Paul has identified your problem, but some further information would be helpful in suggesting a long-term solution. Is this a one-time project, or do you need to do this periodically, and if so how frequently. If you need to do it every week, a better solution would be to create a procedure in your database that simply lets you click a button to create the updated version. On the other hand, if you do it once a year, the Excel approach RG gave you should work - if you have an Excel Workbook called NewList.xlsx.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    To run it from Access, your code would be a lot simpler, something like:

    set rs=currentdb.openrecordset("SELECT CompanyID, ZipCode FROM tblCompany WHERE ZipCode Is Not Null",dbopendynaset)

    do while rs.eof = false
    varzip = split(rs!ZipCode,",")
    For N=0 to ubound(varzip)
    currentdb.execute "INSERT INTO tblCompanyZip (CompanyID, Zip) VALUES (" & rs!CompanyID & "," & varzip(n) & ")"
    Next
    rs.movenext
    loop
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    MarkLiquorman - Thanks you for your help! This has significantly helped answer my questions.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Glad to help. Good luck with it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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
  •