Re: Truncated Strings
- From: "tpkt" <tpkt@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Sep 2005 08:35:11 -0700
Ok.
Its the end of the day over here, so I will wait till I am home to try it,
Thanks,
K
"Allen Browne" wrote:
> 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.
>
> --
> 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.
>
> "tpkt" <tpkt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:7E6C6699-551B-4CAA-BB09-BA69024A1DE2@xxxxxxxxxxxxxxxx
> > 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
> >
> >
> > "Allen Browne" wrote:
> >
> >> Adding more characters after the closing semicolon won't be very
> >> successful.
> >>
> >>
> >> "tpkt" <tpkt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:7CD80C06-F308-4D37-BDC0-35050D70EA0F@xxxxxxxxxxxxxxxx
> >> > 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
> >> > "Allen Browne" wrote:
> >> >
> >> >> Explain how you create this string.
> >> >> Are you tpying it into a VBA module?
> >> >>
> >> >> Also, in what version of Access is this problem happening?
> >> >>
> >> >> "tpkt" <tpkt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:EB38EFEC-78C0-4007-959D-3EACAD34FA68@xxxxxxxxxxxxxxxx
> >> >> >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
> >> >> >
> >> >> > "Allen Browne" wrote:
> >> >> >
> >> >> >> The limit must be elsewhere. JET can handle SQL strings up to 64K
> >> >> >> characters, and VBA can handle 2-billion character strings.
> >> >> >>
> >> >> >> "tpkt" <tpkt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> >> news:8371C0D6-C042-4EE0-B337-EBD94A31CDD7@xxxxxxxxxxxxxxxx
> >> >> >> >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
>
>
>
.
- Follow-Ups:
- Re: Truncated Strings
- From: tpkt
- Re: Truncated Strings
- References:
- Truncated Strings
- From: tpkt
- Re: Truncated Strings
- From: Allen Browne
- Re: Truncated Strings
- From: tpkt
- Re: Truncated Strings
- From: Allen Browne
- Re: Truncated Strings
- From: tpkt
- Re: Truncated Strings
- From: Allen Browne
- Re: Truncated Strings
- From: tpkt
- Re: Truncated Strings
- From: Allen Browne
- Truncated Strings
- Prev by Date: Re: Referencing a query value in a form field
- Next by Date: Loop thru records in a table
- Previous by thread: Re: Truncated Strings
- Next by thread: Re: Truncated Strings
- Index(es):
Relevant Pages
|
