Are VBA strings really small? Or am I doing something wrong.

[Guest]

I am building up a complex SQL statement like this...

Dim sql As String
sql = "SELECT Broker, SUM(cntMTD) AS cntMTD, SUM(cntYTD) as cntYTD,"
sql = sql + "SUM(USDrealMTD) AS USDrealMTD,,"
sql = sql + "SUM(cadMTD) AS cadMTD, SUM(usdMTD) AS usdMTD"
[etc]
Me.RecordSource = sql

I use this technique all through my app, but this one doesn't work and when
I look at it in the debugger, it's truncated at about the 255 character point.

Is there some easy way to make this longer so it won't get truncated? Any
why don't I get an error when I'm trying to ADD to the string, instead of it
just invisibly failing?

Maury

 

[Guest]

Maury,

String variables can be up to 1 billion characters. In Access 2000, the
limit for an SQL string assigned to the RecordSource property was 2048
characters, increased to 32,000 in Access 2002. I don't know what the limit
was prior to that, but I suspect there is some other logical error in your
string assignment.

You may want to either insert MsgBox sql statements after each line, or use
the continuation character to assign sql in one fell swoop:

sql = "SELECT Broker, SUM(cntMTD) AS cntMTD, SUM(cntYTD) as cntYTD," & _
"SUM(USDrealMTD) AS USDrealMTD,," & _
"SUM(cadMTD) AS cadMTD, SUM(usdMTD) AS usdMTD" & _
.... etc.

Hope that helps.

Sprinks

 

[Albert D.Kallal]

This is clearly a bug. I'd report it, but of course MS only allows you to

report bugs via snail mail, so I won't bother.

Try replacing the "&" with a +...perhaps you having something being valued
as a number, or a null.

you get a null string if you go

(some text Value ) + (null) = nulll

if you use &, then nulls don't propagate through the string...

(some text value) & (nulll) = (some text value).

I suspect that you have a problem with concentration..and a text value is
either being valued as a number...and not a string...or as above...some null
value is in the string....

A quick run though your code in the debugger should realize where/what line
your original code fails on...

not convinced it is a bug at all.....

 

[Guest]

Albert,

Thanks for the insight. I suspect you're right.

Sprinks