VBA string bug?

mmcdonal · March 30th, 2009, 08:23 AM

There should be a better systemic fix for this rather than treating the symptoms, but what happens when you do this:

Code:

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

Also, in the first line here, shouldn't it be:

strSQL = "SELECT " & strOmschrijving & " AS Omschrijving, " & CStr(V.Jaar) + ' ' + Format(V." & strPeriode & _

Also, is strID returning a column name?

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

The CStr is in the right place there (it converts a numeric column to string in the query itself). strID is just a number is converted to string.

But don't try figuring out what this exact SQL statement means, because it's not that only this line fails. It's some sort of bug, because it can happen in code line x at this moment and when i work around it it might fail in line y, or line z in another module. But it only happens when concatenating strings.

mmcdonal · March 30th, 2009, 08:47 AM

This line is still wrong:

strSQL = "SELECT " & strOmschrijving & " AS Omschrijving, CStr(V.Jaar) + ' ' + Format(V." & strPeriode

Should be:

strSQL = "SELECT " & strOmschrijving & " AS Omschrijving, " & CStr(V.Jaar) & "' '" & Format(V." & strPeriode & ",'00') AS Bereik1, "

Can you output this to a messagebox to see the formed string?

steeffie · March 30th, 2009, 08:55 AM

I think you did not read the original post completely or do not understand me. The code is fine, it used to work, but now it sometimes does and sometimes it doesn't.

It might be this line today, another one tomorrow. For instance this morning it failed to this correctly:

Code:

strSQL = IIf(blnPreSelectie, "WHERE", "AND")

After this statement (and I stepped through it while debugging) strSQL was empty!! (As in "" or vbNullstring.) There is not any situation where strSQL could be empty as blnPreSelectie is of boolean type.

mmcdonal · March 30th, 2009, 09:12 AM

This:

strSQL = IIf(blnPreSelectie, "WHERE", "AND")

should be?:
Dim blnPreSelectie As Boolean

blnPreSelectie = False

...
strSQL = strSQL & IIf(blnPreSelectie, "WHERE", "AND")

Anyway, I don't think you would run into a situation where you had disallowed characters, at least not in a string variable. Are there disallowed or overflows in other variable types you are taking? For example, I see you refer to an ID value. Is that taken into a string, integer or long variable type initially? Well, if that were the case, you should get an overflow error at least before you got to the string concatenation issue.

Perhaps if you break your code down into long hand and end up with your sql string built totally from string values. For example:

Dim intValue As Integer
Dim sInt As String
Dim sSQL As String

intValue = Me.IntegerField
sInt = CStr(intValue)

sSQL = "... sInt ..."

Then see if the other variables are throwing errors periodically. I know Integer types are especially problematic.

I am still looking elsewhere for a resolution.

steeffie · March 30th, 2009, 09:18 AM

I really dont think its an issue concerning overflows or whatever because it randomly happens in random places. For instance at this moment I'm not having any problems/errors. But probably, after adding some code, there will be some line of code with strings that will not function as it should.

Look, I did not include all lines of code. You have to believe me if I say that any variable is declared correctly, when I need an integer or whatever other datatype in a string I alweays explicitly convert it to string, etc. I have 10yrs experience as a VB developer (I'm a MCSD VB6) so I think I know what Im doing). This is by far the strangest think Ive ever seen in my VB/VBA history.

mmcdonal · March 30th, 2009, 09:25 AM

I believe that you have loads of experience with this, but without seeing all the code, other posters will need to work through their own troubleshooting steps with it. I can see this is a real head scratcher. Most of the stuff I am finding on intermittent code problems deal with values as the string is built. Access has a problem building strings from fields and calculations at execution time, and the solution is to break out the concatenation shorthand and build the string before execution, but you seem to be doing that already.

For example, Access has a problem with:

DoCmd.OpenForm sDoc, , , "[ID] = " & Me.ID

But not:

sSQL = "[ID] = " & Me.ID

DoCmd.OpenForm sDoc, , , sSQL

Sometimes the first form will work, and sometimes it won't. But you seem to be past that issue.

Still looking...

steeffie · March 30th, 2009, 09:31 AM

Thx for taking time.

And indeed my problem seems a little different. Although it's a strange problem, Im surprised that I seem to be the first to have this. Or no-one really wants to talk about it on the internet ;-)

I probably wont get an answer here, but was hoping someone had similar problems.

gbianchi · March 30th, 2009, 09:42 AM

Do you have a "on error resume next"?

Maybe is a memory problem. You run out of it????

could you check that out?

mmcdonal · March 30th, 2009, 12:09 PM

That was where I was going with the data types comments. Improper data types will cause intermittent overflow errors. On Error Resume Next will just cover them up. Is that what you were suggesting?