Truncated Strings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tpkt - 12 Sep 2005 10:15 GMT

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

Reply to this Message

Allen Browne - 12 Sep 2005 10:36 GMT

The limit must be elsewhere. JET can handle SQL strings up to 64K
characters, and VBA can handle 2-billion character strings.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am trying to build a (SQL) string in a piece of vb code that will be used
> to open a recordset.
[quoted text clipped - 5 lines]
> Thank you
> K

Reply to this Message

tpkt - 12 Sep 2005 10:50 GMT

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

> The limit must be elsewhere. JET can handle SQL strings up to 64K
> characters, and VBA can handle 2-billion character strings.
[quoted text clipped - 8 lines]
> > Thank you
> > K

Reply to this Message

Allen Browne - 12 Sep 2005 12:38 GMT

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

Also, in what version of Access is this problem happening?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I realise this.. but where else can it be?
> I found this problem out by watching the string and copying and pasting
[quoted text clipped - 20 lines]
>> > Thank you
>> > K

Reply to this Message

tpkt - 12 Sep 2005 13:28 GMT

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

> Explain how you create this string.
> Are you tpying it into a VBA module?
[quoted text clipped - 25 lines]
> >> > Thank you
> >> > K

Reply to this Message

Allen Browne - 12 Sep 2005 13:58 GMT

Adding more characters after the closing semicolon won't be very successful.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The error actualy occurs in a module that I was playing around with that
> has
[quoted text clipped - 67 lines]
>> >> > Does anyone know why this would be?
>> >> > Thank you

Reply to this Message

tpkt - 12 Sep 2005 14:26 GMT

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

> Adding more characters after the closing semicolon won't be very successful.
>
[quoted text clipped - 69 lines]
> >> >> > Does anyone know why this would be?
> >> >> > Thank you

Reply to this Message

Allen Browne - 12 Sep 2005 15:55 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm not.
> I am not trying to add characters where the ***, I am trying to add the
[quoted text clipped - 95 lines]
>> >> >> > Does anyone know why this would be?
>> >> >> > Thank you

Reply to this Message

tpkt - 12 Sep 2005 16:35 GMT

Ok.
Its the end of the day over here, so I will wait till I am home to try it,
Thanks,
K

> 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.
[quoted text clipped - 127 lines]
> >> >> >> > Does anyone know why this would be?
> >> >> >> > Thank you

Reply to this Message

tpkt - 14 Sep 2005 13:51 GMT

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

> Ok.
> Its the end of the day over here, so I will wait till I am home to try it,
[quoted text clipped - 132 lines]
> > >> >> >> > Does anyone know why this would be?
> > >> >> >> > Thank you

Reply to this Message

Allen Browne - 14 Sep 2005 16:09 GMT

Good news! You have it solved.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Alan,
> the problem was with my logic in a piece of code further up. However if
[quoted text clipped - 3 lines]
> leading
> to the confusion.

Reply to this Message