String truncation Question

[Dylan]

I have a variable that keeps getting truncated when I try to set it. Is there a limit to size of string ??? How can I get this whole string in a varibale

Thanks for any help


dim sSql as string

sSql = "SELECT tbl_Event.*, tbl_Task.*, tbl_UnitNames.* FROM (tbl_Event INNER JOIN tbl_Task ON tbl_Event.Event_ID = tbl_Task.Event_ID) INNER JOIN (tbl_UnitNames INNER JOIN tbl_UnitsAssigned ON tbl_UnitNames.UnitNames_ID = tbl_UnitsAssigned.UnitNames_ID) ON tbl_Task.Task_ID = tbl_UnitsAssigned.Task_ID where "


Tom Moran
Lansing, Michigan

 

[RoyVidar]

String like that can take approx 2 billion characters, if I'm remembering correct, but it's sometimes easier to assist when we know what the problem is, ref faq181-2886...

Is it just that you don't concatenate the string?

[tt]sSql = "SELECT tbl_Event.*, tbl_Task.*, tbl_UnitNames.* " & _
"FROM (tbl_Event INNER JOIN tbl_Task ON tbl_Event.Event_ID = " & _
"tbl_Task.Event_ID) INNER JOIN (tbl_UnitNames INNER JOIN " & _
"tbl_UnitsAssigned ON tbl_UnitNames.UnitNames_ID = " & _
"tbl_UnitsAssigned.UnitNames_ID) ON tbl_Task.Task_ID = " & _
"tbl_UnitsAssigned.Task_ID where "[/tt]

or is it the missing where clause?

Roy-Vidar

 

[Dylan]

The problem is When I set the string with the stmt in the example it gets truncated.

Tom Moran
Lansing, Michigan

 

[Dylan]

this is what ends up in the string


sSql : "SELECT tbl_UnitNames.UnitName, tbl_Event.*, tbl_Task.*, tbl_UnitsAssigned.*, tbl_UnitNames.* FROM (tbl_Event INNER JOIN tbl_Task ON tbl_Event.Event_ID=tbl_Task.Event_ID) INNER JOI

Tom Moran
Lansing, Michigan

 

[RoyVidar]

How do you determine that?

By doing a msgbox sSql or debug.print sSql?

Roy-Vidar

 

[lupins46]

Yes there is a limit to a string size but that isn't the problem here as the limit is 2 billion characters.

There is a limit of 32k characters for an sql statement run in code, but again that doesn't seem to apply here.

I think you might need to post more of the code that is building the string.

 

[Dylan]

The sSql string is what is getting truncated ...
Here is the code
============================================================
Set ctlList = Me.Units

sSql = "SELECT tbl_Event.*, tbl_Task.*, tbl_UnitNames.* FROM (tbl_Event INNER JOIN tbl_Task ON tbl_Event.Event_ID = tbl_Task.Event_ID) INNER JOIN (tbl_UnitNames INNER JOIN tbl_UnitsAssigned ON tbl_UnitNames.UnitNames_ID = tbl_UnitsAssigned.UnitNames_ID) ON tbl_Task.Task_ID = tbl_UnitsAssigned.Task_ID where "

For Each Lmnt In ctlList.ItemsSelected
sSql = sSql & "UnitName= '" & ctlList.ItemData(Lmnt) & "' OR "
Next

Stop


Tom Moran
Lansing, Michigan

 

[Dylan]

Roy,

I determined it by a watch smt and putting a STOP in the code.

Tom Moran
Lansing, Michigan

 

[PHV]

How is Dim'ed sSql ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[oharab]

I could guess that the limit is in the "watch box" only showing you the first how ever many characters.
Before your stop command, try

debug.print sSql

and the full sql statement will be printed in the immediate window (ctrl+G if it's not visible).

Does the full SQL statement appear there?

Ben.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F