VBA string bug?

#1

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.

#2

On Mar 26, 3:20*am, stefan.k... (AT) gmail (DOT) com wrote:

Quote:

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.

I tried both examples in access97 and stepping through the statements
works fine

#3

stefan.krul (AT) gmail (DOT) com wrote:

Quote:

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.

You may need to split it up differently:

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

Quote:

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

VBA string bug?

comp.databases.ms-access comp.databases.ms-access

VBA string bug? - 03-26-2009 , 03:20 AM

Re: VBA string bug? - 03-26-2009 , 03:34 AM

Re: VBA string bug? - 03-26-2009 , 06:55 AM