Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: veritcal concatenations

    Hi guys!!!!
    I'm in need of a piece of your knoledge:
    as from the title I need to concatenate vertically some strings stored in the same fields, in order to obtain one field which is the concatenation of all the fields interested.
    The fact is that i need to concatenate those string basing on some criteria(basically those who have the same name in the first filed!!!!)
    in the attached there are the picture of the table: in the step1 there is just the imagine of my toriginal table
    in the step2 there is the first pat of the concatenation process that i'd like to implement
    the vba code that i've created is to pass from step1 to step2 is here:
    Code:
    Sub count()
    Dim rst As DAO.Recordset
    Dim Length As Long
    Dim I As Long
    Dim N As Long
    Dim lung As Long
    Set rst = CurrentDb.OpenRecordset("xlstab3", dbOpenDynaset)
    With rst
    Dim S(1 To 12)
    For I = 2 To DCount("CDPRD", "xlstab3", "CDPRD = 'PCA'")
    .Edit
    S(1) = !DC
    .Update
    .MoveNext
    .Edit
    S(I) = S(I - 1) & !DC
    .Update
    .MoveNext
    Next
    .MoveFirst
    For N = 1 To DCount("CDPRD", "xlstab3", "CDPRD = 'PCA'")
    .Edit
    !DCC = S(12)
    .Update
    .MoveNext
    Next
    End With
    End Sub
    to obtain the same result i basically need to extend this to the other 12 fields
    Code:
    Sub countPCB()
    Dim rst As DAO.Recordset
    Dim Length As Long
    Dim I As Long
    Dim N As Long
    Dim count As Long
    Dim lung As Long
    Set rst = CurrentDb.OpenRecordset("xlstab3", dbOpenDynaset)
    With rst
    Dim S(1 To 24)
    For I = 14 To 24
    .Edit
    S(13) = !DC
    .Update
    .MoveNext
    .Edit
    S(I) = S(I - 1) & !DC
    .Update
    .MoveNext
    Next
    .Move 13
    Do Until count = DCount("CDPRD", "xlstab3", "CDPRD = 'PCB'")
    .Edit
    !DCC = S(24)
    .Update
    .MoveNext
    count = count + 1
    Loop
    End With
    End Sub
    but the second part doe'st work or at least doesn't work properly!!!!
    Attached Thumbnails Attached Thumbnails 1step.bmp   Step2.bmp  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I did not try to analyze everything in your code, however:

    1. Why do you open the recorset on the whole table (or query) 'xlstab3':
    Code:
    Set rst = CurrentDb.OpenRecordset("xlstab3", dbOpenDynaset)
    While you loop through it using a counter the limit of which is only a subset of the said table?:
    Code:
    For I = 2 To DCount("CDPRD", "xlstab3", "CDPRD = 'PCA'")
    Moreover you have 2 .MoveNext instructions for each iteration of the loop. What happens if [DCount("CDPRD", "xlstab3", "CDPRD = 'PCA'")] yields a number that is greater than 1/2 * the total number of records?

    2. Why do you edit and update the current row of that recordset while you change nothing to it (you only read a value and store it into an array):
    Code:
    .Edit
    S(1) = !DC
    .Update
    .MoveNext
    .Edit
    S(I) = S(I - 1) & !DC
    .Update
    .MoveNext
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    thak you for your answer, I'll try to give more information:
    1) the recordset on the entire table it's useless but I do think it's not an error...
    2) this is the mine problem; I have to concatenate vertically all the records in the field "CD" which have the same values in the field CDPRD! the fact is that it works well for the first set of records, producing exactly what i'd like to have as output; but doesn't work properly for the second block of code;
    I've checked this second box with msgBox, the fact is that the vector S(24) has in memory my output so the issue here is the assign statemant that fails:
    in other words something goes wrong here:
    Code:
    Move 13
    Do Until count = DCount("CDPRD", "xlstab3", "CDPRD = 'PCB'")
    .Edit
    !DCC = S(24)
    .Update
    .MoveNext
    count = count + 1
    Loop
    End With
    End Sub
    3) about your 3rd question .... the fact is that in order to create a recursive concatenation i need to initialize the first first position of the array, i don't know if this would happen without the edit update statement!!!!!
    Last edited by jsirico; 02-14-12 at 05:54.

Posting Permissions

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