Problem with strings

[steeffie]

I'm developing an Access application with which reports can be generated dynamically. The SQL I need I create in VBA, because the reports can be grouped and filtered on multiple things. The problem is that when concatenating strings sometimes little parts seem to get lost or overlooked.

For instance:

Here is a part of the code I use to build up the SQL

strSQL = strSQL & _
        "INNER JOIN DeelProcesProcesCode AS DP ON DP.ProcesID = P.ProcesID) " & _
        "INNER JOIN SubSelectiePlanning AS PL ON PL.ProcesCode = DP.ProcesCode) "
strSQL = strSQL & _
        "LEFT JOIN SubSelectieVolume AS V ON V.SorteerCentrumID = PL.SorteerCentrumID " & _
        [B][U]"AND V.ProcesCode = PL.ProcesCode AND V.PeriodeID = PL.PeriodeID AND V.Datum = PL.Datum) "[/U][/B]
strSQL = strSQL & _
        "LEFT JOIN SubSelectieProcesInzet AS I ON I.SorteerCentrumID = PLI.SorteerCentrumID " & _
        "AND I.ProcesCode = PLI.ProcesCode AND I.PeriodeID = PLI.PeriodeID AND I.Datum = PLI.Datum "

When I am done creating the SQL and firing it, the Bold-part is missing. But, it's not always this part that is missing, when I decompile and clean-up the database, etc. it might end up leaving another part of the string out or end up with the correct string.

Anyone familiar with Access VBA having trouble to concatenate strings correctly?

I'm a VB6 MCSD, so I think I know my ways in VBA as well.

 

[mary.h]

Your concatenation looks ok to me. So I just had an idea on the length of the string. Probably you add such a long text, that the string variable can't hold it.

Regards. Mary

 

[steeffie]

It looks like Access has trouble with large strings without newline characters. I've added a bunch of vbNewlines and until now everything seems to work fine.