Truncated Strings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a (SQL) string in a piece of vb code that will be used
to open a recordset.
However if the string length becomes longer than 250ish characters it gets
truncated, meaning that an error occurs when the record set is opened. I am
using access 2000.
Does anyone know why this would be?
Thank you
K
 
The limit must be elsewhere. JET can handle SQL strings up to 64K
characters, and VBA can handle 2-billion character strings.
 
I realise this.. but where else can it be?
I found this problem out by watching the string and copying and pasting its
contents to word it def. appears to be getting truncated.

Anyone else ofer suggestions?

Thanks
 
Explain how you create this string.
Are you tpying it into a VBA module?

Also, in what version of Access is this problem happening?
 
The error actualy occurs in a module that I was playing around with that has
been adapted from your own stock chack function,
found on your own website.

the point where it fails is in this section:

strSql = "SELECT Sum(tblPurchaseOrderDetail.TotQtyReceived) AS QuantityAcq
" & _
"FROM tblPurchaseOrder INNER JOIN tblPurchaseOrderDetail ON
tblPurchaseOrder.PurchaseOrderID " & _
"= tblPurchaseOrderDetail.PurchaseOrderID " & _
"WHERE ((tblPurchaseOrderDetail.ProductID = " & lngProduct & ")"

If Len(strDateClause) = 0 Then
strSql = strSql & ");"
Else
'strSql = strSql & " AND (tblPurchaseOrder.OrderDate " &
strDateClause & "));" '****
''should be this line (above) but this causes string to be too
long
strSql = strSql & ");"
End If

Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If

when I append the part that is commented out and marked with ***, the
application crashes. I put a watch on strSql and found that the string
appeared to be getting cut off near the end. I played with a few lines of
code and found that if strsql contained more than 252 chars then it was being
truncated at 252.

It is in access 2000.

Thank you
 
I'm not.
I am not trying to add characters where the ***, I am trying to add the line
" AND (tblPurchaseOrder.OrderDate " & strDateClause & "));"
to the end of strSql.

but when i print the watch, the following appears

"Contents of strSql" AND (tb

Ie the line " AND (tblPurchaseOrder.OrderDate " & strDateClause & "));"

only partially gets added. - even if I change the line being added.

I know I aint explaining it too well...
Thank you
 
Okay, firstly I'd just like to assure you that the string can be many
characters long, so the problem is not with VBA or JET SQL.

There is a finite limit to the number of line continuation characters you
can use, but your example doesn't have enough for that to be an issue
either.

It is important to edit these strings only while the form is open in
*design* view, not while it is in use, as that can corrupt the database. In
case that has happened:

1. Compact the database:
Tools | Database Utilities | Compact.

2. Close Access.

3. Decompile a copy of the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Compact again.

5. Open the code window, and check that the code compiles (Compile on Debug
menu.) Then try editing the string again.

6. Before you try to OpenRecordset, dump the string to the Immediate window
with:
Debug.Print strSql
to see what Access is making of it.
 
Alan,
the problem was with my logic in a piece of code further up. However if you
set up a watch, the watch is only capable of displaying the first 255
characters in a string and not the full string and this is what was leading
to the confusion.

thanks
KM
 
Back
Top