VBA string bug?

steeffie · March 26th, 2009, 05:17 AM

I've got an Access application which is behaving rather strange. It has trouble handling strings correctly in VBA at random places.

For example:

Code:

strSQL = "SELECT " & strOmschrijving & " AS Omschrijving, CStr(V.Jaar) + ' ' + Format(V." & strPeriode
strSQL = strSQL & ",'00') AS Bereik1, " & CStr(intNiveau) & " AS Niveau, " & strID & " AS ID, "
strSQL = strSQL & CStr(GeefMaxSortering + 1) & " + (3000-V.Jaar)*10000 - V." & strPeriode & " AS Sortering, " & vbNewLine

When stepping through the code it leaves out the bold part " AS ID, ". But when I add an extra space somewhere in that line, than it behaves correctly.

Similar problems in other parts of the application too. I've already tried decompiling etc, to clean things up. I even made a new mdb and imported all objects and code, but no effect.

Has someone every seen anything like this too? I cannot imagine being the first to have this problem.

Another example would be:

Code:

strSQL = "SELECT B.SorteerCentrumID, B.PeriodeID, PER.Jaar, PER.Week, PER.Tijdvak, B.Datum, B.ProcesCode, "
strSQL = strSQL & "Sum(Nz(B.WaardeOchtend,0)) AS WaardeOchtend, Sum(Nz(B.WaardeMiddag,0)) AS WaardeMiddag, " & vbNewLine
strSQL = strSQL & "Sum(Nz(B.WaardeAvond,0)) AS WaardeAvond, Sum(Nz(B.WaardeNacht,0)) AS WaardeNacht, "
strSQL = strSQL & IIf(blnIsSorteerdag, "Sum(Nz(B.WaardeNachtVolgend,0)) AS WaardeNachtVolgend ", "NULL AS WaardeNachtVolgend ")

Here it leaved out the entire first line. When stepping through the code the string would still be empty after executing only the first line. But again, by adding a space somewhere inside the string, it would work again.

This is Access 2003 SP3, VBA6.3.

gbianchi · March 26th, 2009, 07:55 AM

Hello.. IF your string is defined like strSQL as string, then this is a first time. Are you sure that in no other place you are touching this strings?

steeffie · March 26th, 2009, 08:19 AM

Pls look at example 2. When I step through code and execute the first line, strSQL stays empty. Im developing in VB(A) (MCSD VB6) for more than 10 yrs now and havent seen anything like this before.

gbianchi · March 26th, 2009, 08:24 AM

Me neither, that why I asked you... Just a stupid question, what if you change the lines, I mean put the first line into the second position and try that (I know It will make the SQL useless, I want you to try if maybe there is a problem with the compiler...)

steeffie · March 26th, 2009, 08:29 AM

I've added a empty line before the line and then it works. But this will not be a lasting solution, changes are that now some code in some other module wille have similar problem.

gbianchi · March 26th, 2009, 08:35 AM

Did you find anything on google?? I really never saw a problem like that...
What if you try that in a new database?? do it work fine?? Maybe the database is corrupt and that why is working bad...

steeffie · March 26th, 2009, 08:46 AM

Ive tried a complete new database as well. No help. Have searched Google extensively, no help. Although i did find one topic somewhere where someone had similar problem, but no real solution was given other than rewriting query.

http://techreport.com/forums/viewtopic.php?f=20&t=62576

Believe me, Ive tried anything now.

gbianchi · March 26th, 2009, 09:02 AM

Ok, Another weird Idea.. what if you change the type of strSql to variant?? do it change anything???

steeffie · March 26th, 2009, 09:43 AM

Does not help.

gbianchi · March 26th, 2009, 09:46 AM

And in another PC?