locked
Help with concatrelated vba excel RRS feed

  • Question

  • 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'"
    Friday, October 12, 2012 7:40 PM

All replies

  • I assume that you have copied the code for ConcatRelated from http://allenbrowne.com/func-concat.html into a module in the Visual Basic Editor.

    The arguments to ConcatRelated must be strings, so try

    MYSQL = MYSQL & "CONCATRELATED(""EXCCEX"", ""E01"", ""EXCNRO="" & [CABNRO]) "

    This assumes that EXCNRO is a number field. If it is a text field, use

    MYSQL = MYSQL & "CONCATRELATED(""EXCCEX"", ""E01"", ""EXCNRO="" & Chr(34) & [CABNRO] & Chr(34)) "


    Regards, Hans Vogelaar

    Friday, October 12, 2012 7:50 PM
  • Hans, thank you for your answer, I tried both ways but the programm is not still working. The message that appears tells that "symbol ( is not valid". Let me know if you need further information, thanks again for your help. One more thing, I don't know if I have to use a "reference" in order to be able to use CONCATRELATED function, Do I have to use a reference?
    Monday, October 15, 2012 5:08 PM
  • No, you don't need to set a reference. It's sufficient to copy the code for ConcatRelated into a module in the Visual Basic Editor.

    Try

    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"", ""EXCNRO="" & A.CABRNO) "
    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'"


    Regards, Hans Vogelaar

    Monday, October 15, 2012 7:13 PM
  • Thanks again Hans, I tried the new code but the result still remains as an error, the message is the same ["symbol ( is not valid"]. Let me know if you need further information. Regards. Gustavo
    Tuesday, October 16, 2012 5:01 PM
  • It's too hard to tell what goes wrong without seeing the database.

    Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar

    Tuesday, October 16, 2012 7:10 PM