Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Exclamation Unanswered: Help with concatrelated vba excel

    Hi, I'm having problem using the function concatrelated on a sql statement. I want to obtain as follows:

    My data is:
    A.CABNRO E01.EXCCEX
    90001 500
    87000 400
    90001 450
    90001 300
    87000 500
    76005

    I want to obtain:
    A.CABNRO NEW FIELD
    90001 500,450,300
    87000 400,500
    76005

    The following code does not run when I include the statement related with concatrelated. I will be grateful if someone can help me!!!

    MYSQL = "SELECT A.CABFPR, A.CABCIA, A.CABCTR, B.CTRDTR, A.CABLOE, "
    MYSQL = MYSQL & "A.CABNRO, A.CABCAR, A.CABFED, A.CABHOD, A.CABFEI, "
    MYSQL = MYSQL & "A.CABHOI, A.CABFEF, A.CABHOF, A.CABFEE, A.CABHOE, "
    MYSQL = MYSQL & "A.CABCCL, A.CABNEM, A.CABDEM "
    MYSQL = MYSQL & "CONCATRELATED(EXCCEX, E01, A.CABNRO=E01.EXCNRO) "
    MYSQL = MYSQL & "FROM BDYOBEL.AIPDTA.AIPCAB A "
    MYSQL = MYSQL & "LEFT OUTER JOIN BDYOBEL.AIPDTA.AIPCTR B ON A.CABCIA=B.CTRCIA AND A.CABCTR=B.CTRCTR "
    MYSQL = MYSQL & "LEFT OUTER JOIN BDYOBEL.AIPDTA.AIPEXC E01 ON A.CABNRO=E01.EXCNRO "
    MYSQL = MYSQL & "WHERE A.CABCIA='LOR' AND A.CABFED>="
    MYSQL = MYSQL & Chr$(39) & fini & Chr$(39) & "AND A.CABFED<=" & Chr$(39) & ffin & Chr$(39)
    MYSQL = MYSQL & " AND (A.CABCTR='DM' OR A.CABCTR='L1' OR A.CABCTR='L5' OR A.CABCTR='LR') AND A.CABCAR<>'0'"

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Excel doesn't have any means of executing SQL statements.
    ConcatRelated is a user-defined function, apparently written by Allen Browne for Access.

    How are you running this query?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    Thanks for your answer, Im running on excel as a macro. Is that what you mean? Regards. Gustavo

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    As I said, Excel has no native way to execute SQL statements. Also, on closer examination, the function itself will not work in Excel as it is written for Access.

    You will need to write your own subroutine that operates within the constraints of Excel to achieve your stated output.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

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
  •