VBA Express : Excel - Use of CONCATRELATED function on SQL statements

Excel

Use of CONCATRELATED function on SQL statements

Ease of Use

Hard

Version tested with

2007

Submitted by:

Gus0411

Description:

The outcome of the run is an error, when I eliminate the sentence related with concatrelared the programs runs without any problem.

Discussion:

I'm trying to use "concatrelated" function in order to concatenate the following example: A.CABNRO E01.EXCCEX 980012 500 767030 400 980012 200 980012 350 767030 250 The result that I wanna acomplished is as follows: A.CABNRO NEW FIELD 767030 400, 250 980012 500, 200, 350 I would like to know what I'm doing wrong, Do I have to add some new reference? Does "concatrelated works on VBA Excel?

Code:

instructions for use

			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, E01.EXCCEX " 
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.CABFPR>=" 
MYSQL = MYSQL & Chr$(39) & fini & Chr$(39) & "AND A.CABFPR<=" & Chr$(39) & ffin & Chr$(39) 
MYSQL = MYSQL & " AND (A.CABCTR='DM' OR A.CABCTR='L1' OR A.CABCTR='L5' OR A.CABCTR='LR')"

How to use:

Test the code:

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 11 times.