Results 1 to 2 of 2

Thread: Help with concatrelated vba excel

  1. #1

    Exclamation Help with concatrelated vba excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    Location
    South Florida, USA
    Posts
    13
    Articles
    0
    Cross-posted from:
    http://social.msdn.microsoft.com/For...6-75973e57ff54

    I'm also assuming you're trying to use this function:
    http://allenbrowne.com/func-concat.html

    I have never heard of ConcatRelated before finding that page.

    Original line:
    Code:
    MYSQL = MYSQL & "CONCATRELATED(EXCCEX, E01, A.CABNRO=E01.EXCNRO) "


    There are a couple things going wrong here:
    1. You need to call this function outside of the query. Currently, you're trying to use CONCATRELATED() as if it were part of the MySQL standard functions, which it isn't.
    2. You're trying to use CONCATRELATED() as part of your results in the main SELECT query, which will not connect as part of your results set, but rather it is likely that it will just repeat the same thing for every row, which I assume you don't want. In MS Access this is possible, but Excel VBA using MySQL? Probably not.


    Here's how it *might* work, but I can't verify:
    Code:
    MYSQL = MYSQL & CONCATRELATED("EXCCEX", "BDYOBEL.AIPDTA.AIPCAB A LEFT OUTER JOIN BDYOBEL.AIPDTA.AIPEXC E01 ON A.CABNRO=E01.EXCNRO", "A.CABNRO=E01.EXCNRO")
    Give that a shot, though I doubt it will work like you need it to.


    To be honest, for this situation, I think your best bet is to execute a regular query that gets you:
    A.CABNRO E01.EXCCEX
    90001 500
    87000 400
    90001 450
    90001 300
    87000 500
    76005

    And then run a VBA script that will transpose it for you. We can help you with that if you like.

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
  •