Help with concatrelated vba excel

gus0411 · Oct 12, 2012

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'"

joseph4tw · Oct 15, 2012

Cross-posted from:
http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/6a3810a5-73b8-46e8-9676-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:

[COLOR=#000000][FONT=verdana]MYSQL = MYSQL & "CONCATRELATED(EXCCEX, E01, A.CABNRO=E01.EXCNRO) "[/FONT][/COLOR]

There are a couple things going wrong here:

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.
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:

[COLOR=#000000][FONT=verdana]MYSQL = MYSQL & CONCATRELATED("EXCCEX", "[/FONT][/COLOR][COLOR=#000000][FONT=verdana]BDYOBEL.AIPDTA.AIPCAB A LEFT OUTER JOIN [/FONT][/COLOR][COLOR=#000000][FONT=verdana]BDYOBEL.AIPDTA.AIPEXC E01 ON A.CABNRO=E01.EXCNRO[/FONT][/COLOR][COLOR=#000000][FONT=verdana]", "A.CABNRO=E01.EXCNRO")[/FONT][/COLOR]

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.

Help with concatrelated vba excel

gus0411

New member

joseph4tw

New member