String variable only storing 256 characters.

[RandyJ]

Good Afternoon,

I'm trying to store a long string into a string variable, but it seems to only retain around 256 characters. In looking at the Access VB reference regarding String Data Types, it suggests that this data type will hold up to 64,000 characters. Am I misunderstanding? I've also tried to dim the variable as a variant, but that didn't help either. Maybe I'm populating the variable incorrectly. Here my code just in case.

strSQL = strSQL & "SELECT tblProduct_Line.str_product_line, " & _
"qrySupervisor.SuperName, qryAnalystLast.AnalystName, " & _
"tblSession_Head.dte_call_date, tblSession_Head.dte_review_date, " & _
"tblMethod.str_method_name "

'From statement
strSQL = "FROM (qrySupervisor INNER JOIN (qryAnalystLast INNER JOIN " & _
"(tblMethod INNER JOIN tblSession_Head ON tblMethod.lng_method_id = " & _
"tblSession_Head.lng_method_id) ON qryAnalystLast.lng_analyst_id = " & _
"tblSession_Head.lng_analyst_id) ON qrySupervisor.lng_analyst_id = " & _
"tblSession_Head.lng_cur_supervisor) INNER JOIN tblProduct_Line ON " & _
"qryAnalystLast.lng_product_line_id = tblProduct_Line.lng_product_line_id "

'Where statement
strSQL = strSQL & "WHERE (((tblSession_Head.dte_call_date) " & _
"Between #" & dteBegin & "# And #" & dteEnd & "#)) "

If Len(strSuper) > 1 Then
strSQL = strSQL & "AND (((tblSession_Head.lng_cur_supervisor) = " & lngSuperId & &quot) "
Else
If Len(strProdLine) > 1 Then
strSQL = strSQL + "AND (((tblProduct_Line.lng_product_line_id) = '" & lngProdLineId & "')) "
End If
End If

'Group By statement
strSQL = strSQL & "GROUP BY tblProduct_Line.str_product_line, " & _
"qrySupervisor.SuperName, qryAnalystLast.AnalystName, " & _
"tblSession_Head.dte_call_date, tblSession_Head.dte_review_date, " & _
"tblMethod.str_method_name "

'Order By statement
strSQL = "ORDER BY "
If boolProdLine Then
strSQL = strSQL + "tblProduct_Line.str_product_line, "
End If
strSQL = strSQL + "qrySupervisor.SuperName, qryAnalystLast.AnalystName, " & _
"tblSession_Head.dte_call_date, " & _
"Format([tblSession_Head]![dte_call_date],'yyyy mm') DESC "

'Pivot statement
strPivot = "PIVOT Format([tblSession_Head]![dte_call_date],'yyyy mm');"

Any advice would be greatly appreciated.

Thanks in advance.
Randy Johnston

 

[RandyJ]

Never mind. I figured out the problem. While in Debug mode, I was copying the Watched Variable Value field and pasting it into UltraEdit. Apparently, that value only displays the first 256 characters.

 

[scriverb]

I am curious. Are you saying that actually the strSQL was in fact storing all of the string but in your monitoring in the debug window you were only seeing the first 255? I am asking this only for information purposes as I have put together long strings of SQL and had no problems.

There was a similar question recently concerning assigning a Memo value field from a combobox additional column to a string variable. I answered that question with the fact that the additional columns in a ComboBox (i.e. me!combobox.column(3)) only pass the first 255 characters.

Just like to catalog this quirky items for further reference.

Bob Scriver