Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #12390 > unrolled thread

Problem with long character strings

Back to article view | Back to comp.databases.ms-access

Contents

  Problem with long character strings internet.shopping@foobox.com - 2017-02-24 06:27 -0800
    Re: Problem with long character strings Patrick Finucane <patrickfinucanetx@gmail.com> - 2017-02-24 06:54 -0800
      Re: Problem with long character strings internet.shopping@foobox.com - 2017-02-24 07:07 -0800
        Re: Problem with long character strings Patrick Finucane <patrickfinucanetx@gmail.com> - 2017-02-24 09:06 -0800
          Re: Problem with long character strings Ulrich Möller <knobbi38@arcor.de> - 2017-02-24 18:42 +0100
      Re: Problem with long character strings Ron Weiner <rw@domain.com> - 2017-02-24 10:08 -0500
        Re: Problem with long character strings internet.shopping@foobox.com - 2017-02-24 07:48 -0800
          Re: Problem with long character strings Ron Weiner <rw@domain.com> - 2017-02-24 11:06 -0500
        Re: Problem with long character strings Ulrich Möller <knobbi38@arcor.de> - 2017-02-24 17:14 +0100
          Re: Problem with long character strings internet.shopping@foobox.com - 2017-02-24 09:35 -0800

#12390 — Problem with long character strings

I am having a problem with handling long character strings, i.e. those over 255 characters in length.

In a table I have a memo field named boq_comment.  In one record it contains the value:

To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to levels and cross-falls.

I can display this in full successfully on a Form even though it is 297 characters long.

I need to access this via a query and when I run that query on its own I can see the whole field.

My problem comes when I access the query in VBA when creating a Recordset.

dim itemRec as DAO.Recordset
set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery Where eDRSID = 1537”)

I need to assign the particular field to another variable such as in

x = itemRec(“boq_comment”)

To check that I am getting the expected value I precede this with

debug.print  itemRec(“boq_comment”)

This produces:

To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 4 ?7????? m [ ?????7????  ????[ ????     ?

Strange characters are appearing after 255 characters.

Why should this happen?  And more particularly how to I overcome this? 

(truncating to the first 255 characters would seem to be a cop out)

Jim

[toc] | [next] | [standalone]

#12392

On Friday, February 24, 2017 at 9:27:21 AM UTC-5, internet...@foobox.com wrote:
> I am having a problem with handling long character strings, i.e. those over 255 characters in length.
> 
> In a table I have a memo field named boq_comment.  In one record it contains the value:
> 
> To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to levels and cross-falls.
> 
> I can display this in full successfully on a Form even though it is 297 characters long.
> 
> I need to access this via a query and when I run that query on its own I can see the whole field.
> 
> My problem comes when I access the query in VBA when creating a Recordset.
> 
> dim itemRec as DAO.Recordset
> set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery Where eDRSID = 1537”)
> 
> I need to assign the particular field to another variable such as in
> 
> x = itemRec(“boq_comment”)
> 
> To check that I am getting the expected value I precede this with
> 
> debug.print  itemRec(“boq_comment”)
> 
> This produces:
> 
> To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 4 ?7????? m [ ?????7????  ????[ ????      ?
> 
> Strange characters are appearing after 255 characters.
> 
> Why should this happen?  And more particularly how to I overcome this? 
> 
> (truncating to the first 255 characters would seem to be a cop out)
> 
> Jim

I ran a test on my computer.  I have Access 2013.  It works as expected, I did not get your results.  You don't say what version of Access you have.  You need to verify the table field is set to LONG TEXT for something over 255 chars or Memo if Access is prior to Access 2007.

[toc] | [prev] | [next] | [standalone]

#12393

I am using Access 2010.  The field in question is a Memo.

Did you actually open a recordset and examine the field?  Can you please show me the code you used.

I can see the full and correct data in a form or by running the query but not when using a recordset

Jim



On Friday, 24 February 2017 14:54:40 UTC, Patrick Finucane  wrote:
> On Friday, February 24, 2017 at 9:27:21 AM UTC-5, internet...@foobox.com wrote:
> > I am having a problem with handling long character strings, i.e. those over 255 characters in length.
> > 
> > In a table I have a memo field named boq_comment.  In one record it contains the value:
> > 
> > To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to levels and cross-falls.
> > 
> > I can display this in full successfully on a Form even though it is 297 characters long.
> > 
> > I need to access this via a query and when I run that query on its own I can see the whole field.
> > 
> > My problem comes when I access the query in VBA when creating a Recordset.
> > 
> > dim itemRec as DAO.Recordset
> > set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery Where eDRSID = 1537”)
> > 
> > I need to assign the particular field to another variable such as in
> > 
> > x = itemRec(“boq_comment”)
> > 
> > To check that I am getting the expected value I precede this with
> > 
> > debug.print  itemRec(“boq_comment”)
> > 
> > This produces:
> > 
> > To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 4 ?7????? m [ ?????7????  ????[ ????      ?
> > 
> > Strange characters are appearing after 255 characters.
> > 
> > Why should this happen?  And more particularly how to I overcome this? 
> > 
> > (truncating to the first 255 characters would seem to be a cop out)
> > 
> > Jim
> 
> I ran a test on my computer.  I have Access 2013.  It works as expected, I did not get your results.  You don't say what version of Access you have.  You need to verify the table field is set to LONG TEXT for something over 255 chars or Memo if Access is prior to Access 2007.

[toc] | [prev] | [next] | [standalone]

#12401

On Friday, February 24, 2017 at 10:07:54 AM UTC-5, internet...@foobox.com wrote:
> I am using Access 2010.  The field in question is a Memo.
> 
> Did you actually open a recordset and examine the field?  Can you please show me the code you used.
> 
> I can see the full and correct data in a form or by running the query but not when using a recordset
> 
I don't see Memo as a field type in Access 2007, Access 2010, Access 2013.  I do see Long Text.  There is no Memo option in Access 2013.

I created a Long Text field called Comment in a table called JunkSub.  I then pasted your field value you are having problems with into it.  Then closed the table.  Here is the code I ran.  I did not have an issue.  It came out perfectly.  Again, I have a memo field but in the table structure it is set to LongText.  ShortText won't do if for me.  

Public Sub JunkComment()
    Dim r As Recordset
    Set r = CurrentDb.OpenRecordset("Select JunkSub.* from JunkSub where JunkNum = """ & "2" & """", dbOpenSnapshot)
    Debug.Print r!Comment
    r.Close
End Sub

[toc] | [prev] | [next] | [standalone]

#12403

Hi Patrick,

Am 24.02.2017 um 18:06 schrieb Patrick Finucane:
> I don't see Memo as a field type in Access 2007, Access 2010, Access 2013.  I do see Long Text.  There is no Memo option in Access 2013.
if you use the table designer of access you will see the memo field 
type. If you use SQL-DDL for creation of your table LONGTEXT is a 
synonym for the SQL-Datatype Text without a given length, which is 
equivalent to MEMO. See Access Help "SQL -Data Types"  for further 
information.

Ulrich

[toc] | [prev] | [next] | [standalone]

#12394

Patrick Finucane presented the following explanation :
> On Friday, February 24, 2017 at 9:27:21 AM UTC-5, internet...@foobox.com 
> wrote:
>> I am having a problem with handling long character strings, i.e. those over 
>> 255 characters in length.
>> 
>> In a table I have a memo field named boq_comment.  In one record it contains 
>> the value:
>> 
>> To fine mill the marked area, to remove the HFS surface and then to plane 
>> 30mm off from the remaining area, removing all waste from the site . Cut 
>> suitable tie joints, sweep then apply bond coat to the surface, overlay the 
>> area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to levels and cross-falls.
>> 
>> I can display this in full successfully on a Form even though it is 297 
>> characters long.
>> 
>> I need to access this via a query and when I run that query on its own I can 
>> see the whole field.
>> 
>> My problem comes when I access the query in VBA when creating a Recordset.
>> 
>> dim itemRec as DAO.Recordset
>> set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery Where 
>> eDRSID = 1537”)
>> 
>> I need to assign the particular field to another variable such as in
>> 
>> x = itemRec(“boq_comment”)
>> 
>> To check that I am getting the expected value I precede this with
>> 
>> debug.print  itemRec(“boq_comment”)
>> 
>> This produces:
>> 
>> To fine mill the marked area, to remove the HFS surface and then to plane 
>> 30mm off from the remaining area, removing all waste from the site . Cut 
>> suitable tie joints, sweep then apply bond coat to the surface, overlay the 
>> area with 30mm of 6mm SMA surf 4 ?7????? m [ ?????7????  ????[ ????      ?
>> 
>> Strange characters are appearing after 255 characters.
>> 
>> Why should this happen?  And more particularly how to I overcome this? 
>> 
>> (truncating to the first 255 characters would seem to be a cop out)
>> 
>> Jim
>
> I ran a test on my computer.  I have Access 2013.  It works as expected, I 
> did not get your results.  You don't say what version of Access you have.  
> You need to verify the table field is set to LONG TEXT for something over 255 
> chars or Memo if Access is prior to Access 2007.

And here with Access 2007.  I used this code

Public Sub GetMemo()
    Dim db As DAO.Database, rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select * FROM myTable where MyID = 57", 
dbOpenDynaset)
    Do While Not rs.EOF
        Debug.Print Len(rs("mymemo")); rs("mymemo")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
End Sub

Which resulted in this following in the Immediate window.

 297 To fine mill the marked area, to remove the HFS surface and then 
to plane 30mm off from the remaining area, removing all waste from the 
site . Cut suitable tie joints, sweep then apply bond coat to the 
surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to 
levels and cross-falls.

[toc] | [prev] | [next] | [standalone]

#12396

Thanks.  You are not showing the full 297 characters.

When I try this I get

 297          To fine mill the marked area, to remove the HFS surface and then to plane 30mm off from the remaining area, removing all waste from the site . Cut suitable tie joints, sweep then apply bond coat to the surface, overlay the area with 30mm of 6mm SMA surf 4 ?0????? n K ?????0????  ????K ????     ?

Can you please display the full string shown in your immediate window




On Friday, 24 February 2017 15:08:45 UTC, Ron Weiner  wrote:
> Patrick Finucane presented the following explanation :
> > On Friday, February 24, 2017 at 9:27:21 AM UTC-5, internet...@foobox.com 
> > wrote:
> >> I am having a problem with handling long character strings, i.e. those over 
> >> 255 characters in length.
> >> 
> >> In a table I have a memo field named boq_comment.  In one record it contains 
> >> the value:
> >> 
> >> To fine mill the marked area, to remove the HFS surface and then to plane 
> >> 30mm off from the remaining area, removing all waste from the site . Cut 
> >> suitable tie joints, sweep then apply bond coat to the surface, overlay the 
> >> area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to levels and cross-falls.
> >> 
> >> I can display this in full successfully on a Form even though it is 297 
> >> characters long.
> >> 
> >> I need to access this via a query and when I run that query on its own I can 
> >> see the whole field.
> >> 
> >> My problem comes when I access the query in VBA when creating a Recordset.
> >> 
> >> dim itemRec as DAO.Recordset
> >> set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery Where 
> >> eDRSID = 1537”)
> >> 
> >> I need to assign the particular field to another variable such as in
> >> 
> >> x = itemRec(“boq_comment”)
> >> 
> >> To check that I am getting the expected value I precede this with
> >> 
> >> debug.print  itemRec(“boq_comment”)
> >> 
> >> This produces:
> >> 
> >> To fine mill the marked area, to remove the HFS surface and then to plane 
> >> 30mm off from the remaining area, removing all waste from the site . Cut 
> >> suitable tie joints, sweep then apply bond coat to the surface, overlay the 
> >> area with 30mm of 6mm SMA surf 4 ?7????? m [ ?????7????  ????[ ????      ?
> >> 
> >> Strange characters are appearing after 255 characters.
> >> 
> >> Why should this happen?  And more particularly how to I overcome this? 
> >> 
> >> (truncating to the first 255 characters would seem to be a cop out)
> >> 
> >> Jim
> >
> > I ran a test on my computer.  I have Access 2013.  It works as expected, I 
> > did not get your results.  You don't say what version of Access you have.  
> > You need to verify the table field is set to LONG TEXT for something over 255 
> > chars or Memo if Access is prior to Access 2007.
> 
> And here with Access 2007.  I used this code
> 
> Public Sub GetMemo()
>     Dim db As DAO.Database, rs As DAO.Recordset
> 
>     Set db = CurrentDb()
>     Set rs = db.OpenRecordset("Select * FROM myTable where MyID = 57", 
> dbOpenDynaset)
>     Do While Not rs.EOF
>         Debug.Print Len(rs("mymemo")); rs("mymemo")
>         rs.MoveNext
>     Loop
>     rs.Close
>     Set rs = Nothing
>     Set db = Nothing
>     Exit Sub
> End Sub
> 
> Which resulted in this following in the Immediate window.
> 
>  297 To fine mill the marked area, to remove the HFS surface and then 
> to plane 30mm off from the remaining area, removing all waste from the 
> site . Cut suitable tie joints, sweep then apply bond coat to the 
> surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to 
> levels and cross-falls.

[toc] | [prev] | [next] | [standalone]

#12397

internet.shopping@foobox.com wrote :
> Thanks.  You are not showing the full 297 characters.
>
> When I try this I get
>
>  297          To fine mill the marked area, to remove the HFS surface and 
> then to plane 30mm off from the remaining area, removing all waste from the 
> site . Cut suitable tie joints, sweep then apply bond coat to the surface, 
> overlay the area with 30mm of 6mm SMA surf 4 ?0????? n K ?????0????  ????K 
> ????     ?
>
> Can you please display the full string shown in your immediate window
>


>> Which resulted in this following in the Immediate window.
>> 
>>  297 To fine mill the marked area, to remove the HFS surface and then 
>> to plane 30mm off from the remaining area, removing all waste from the 
>> site . Cut suitable tie joints, sweep then apply bond coat to the 
>> surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV to 
>> levels and cross-falls.

[toc] | [prev] | [next] | [standalone]

#12398

Am 24.02.2017 um 16:08 schrieb Ron Weiner:
> Patrick Finucane presented the following explanation :
>> On Friday, February 24, 2017 at 9:27:21 AM UTC-5, 
>> internet...@foobox.com wrote:
>>> I am having a problem with handling long character strings, i.e. 
>>> those over 255 characters in length.
>>>
>>> In a table I have a memo field named boq_comment.  In one record it 
>>> contains the value:
>>>
>>> To fine mill the marked area, to remove the HFS surface and then to 
>>> plane 30mm off from the remaining area, removing all waste from the 
>>> site . Cut suitable tie joints, sweep then apply bond coat to the 
>>> surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV 
>>> to levels and cross-falls.
>>>
>>> I can display this in full successfully on a Form even though it is 
>>> 297 characters long.
>>>
>>> I need to access this via a query and when I run that query on its 
>>> own I can see the whole field.
>>>
>>> My problem comes when I access the query in VBA when creating a 
>>> Recordset.
>>>
>>> dim itemRec as DAO.Recordset
>>> set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery 
>>> Where eDRSID = 1537”)
>>>
>>> I need to assign the particular field to another variable such as in
>>>
>>> x = itemRec(“boq_comment”)
>>>
>>> To check that I am getting the expected value I precede this with
>>>
>>> debug.print  itemRec(“boq_comment”)
>>>
>>> This produces:
>>>
>>> To fine mill the marked area, to remove the HFS surface and then to 
>>> plane 30mm off from the remaining area, removing all waste from the 
>>> site . Cut suitable tie joints, sweep then apply bond coat to the 
>>> surface, overlay the area with 30mm of 6mm SMA surf 4 ?7????? m [ 
>>> ?????7????  ????[ ????      ?
>>>
>>> Strange characters are appearing after 255 characters.
>>>
>>> Why should this happen?  And more particularly how to I overcome this?
>>> (truncating to the first 255 characters would seem to be a cop out)
>>>
>>> Jim
>>
>> I ran a test on my computer.  I have Access 2013.  It works as 
>> expected, I did not get your results.  You don't say what version of 
>> Access you have.  You need to verify the table field is set to LONG 
>> TEXT for something over 255 chars or Memo if Access is prior to 
>> Access 2007.
>
> And here with Access 2007.  I used this code
>
> Public Sub GetMemo()
>    Dim db As DAO.Database, rs As DAO.Recordset
>
>    Set db = CurrentDb()
>    Set rs = db.OpenRecordset("Select * FROM myTable where MyID = 57", 
> dbOpenDynaset)
>    Do While Not rs.EOF
>        Debug.Print Len(rs("mymemo")); rs("mymemo")
>        rs.MoveNext
>    Loop
>    rs.Close
>    Set rs = Nothing
>    Set db = Nothing
>    Exit Sub
> End Sub
>
> Which resulted in this following in the Immediate window.
>
> 297 To fine mill the marked area, to remove the HFS surface and then 
> to plane 30mm off from the remaining area, removing all waste from the 
> site . Cut suitable tie joints, sweep then apply bond coat to the 
> surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV 
> to levels and cross-falls.
Same results with ACC2010. No truncation of the memo field.

Maybe there are some problems with the underlying query you use. What is 
the sql statement of that query?

Ulrich

[toc] | [prev] | [next] | [standalone]

#12402

Thanks Ulrich, you are so right.

Although I thought I was using the field directly I was actually passing it through a function escapeEmbeddedQuotes() that I had written some time ago.  This implicitly used strings in its code.

I have replaced it with the more sensible use of the replace() function  and it works.

Thanks for others who have also looked at the problem

Jim

It is amazing how you can go round and round in circles and still not look in the right place!!!

On Friday, 24 February 2017 16:14:06 UTC, Ulrich Möller  wrote:
> Am 24.02.2017 um 16:08 schrieb Ron Weiner:
> > Patrick Finucane presented the following explanation :
> >> On Friday, February 24, 2017 at 9:27:21 AM UTC-5, 
> >> internet...@foobox.com wrote:
> >>> I am having a problem with handling long character strings, i.e. 
> >>> those over 255 characters in length.
> >>>
> >>> In a table I have a memo field named boq_comment.  In one record it 
> >>> contains the value:
> >>>
> >>> To fine mill the marked area, to remove the HFS surface and then to 
> >>> plane 30mm off from the remaining area, removing all waste from the 
> >>> site . Cut suitable tie joints, sweep then apply bond coat to the 
> >>> surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV 
> >>> to levels and cross-falls.
> >>>
> >>> I can display this in full successfully on a Form even though it is 
> >>> 297 characters long.
> >>>
> >>> I need to access this via a query and when I run that query on its 
> >>> own I can see the whole field.
> >>>
> >>> My problem comes when I access the query in VBA when creating a 
> >>> Recordset.
> >>>
> >>> dim itemRec as DAO.Recordset
> >>> set itemRec = Currentdb.openRecordset(“Select * From eDRS_BoQQuery 
> >>> Where eDRSID = 1537”)
> >>>
> >>> I need to assign the particular field to another variable such as in
> >>>
> >>> x = itemRec(“boq_comment”)
> >>>
> >>> To check that I am getting the expected value I precede this with
> >>>
> >>> debug.print  itemRec(“boq_comment”)
> >>>
> >>> This produces:
> >>>
> >>> To fine mill the marked area, to remove the HFS surface and then to 
> >>> plane 30mm off from the remaining area, removing all waste from the 
> >>> site . Cut suitable tie joints, sweep then apply bond coat to the 
> >>> surface, overlay the area with 30mm of 6mm SMA surf 4 ?7????? m [ 
> >>> ?????7????  ????[ ????      ?
> >>>
> >>> Strange characters are appearing after 255 characters.
> >>>
> >>> Why should this happen?  And more particularly how to I overcome this?
> >>> (truncating to the first 255 characters would seem to be a cop out)
> >>>
> >>> Jim
> >>
> >> I ran a test on my computer.  I have Access 2013.  It works as 
> >> expected, I did not get your results.  You don't say what version of 
> >> Access you have.  You need to verify the table field is set to LONG 
> >> TEXT for something over 255 chars or Memo if Access is prior to 
> >> Access 2007.
> >
> > And here with Access 2007.  I used this code
> >
> > Public Sub GetMemo()
> >    Dim db As DAO.Database, rs As DAO.Recordset
> >
> >    Set db = CurrentDb()
> >    Set rs = db.OpenRecordset("Select * FROM myTable where MyID = 57", 
> > dbOpenDynaset)
> >    Do While Not rs.EOF
> >        Debug.Print Len(rs("mymemo")); rs("mymemo")
> >        rs.MoveNext
> >    Loop
> >    rs.Close
> >    Set rs = Nothing
> >    Set db = Nothing
> >    Exit Sub
> > End Sub
> >
> > Which resulted in this following in the Immediate window.
> >
> > 297 To fine mill the marked area, to remove the HFS surface and then 
> > to plane 30mm off from the remaining area, removing all waste from the 
> > site . Cut suitable tie joints, sweep then apply bond coat to the 
> > surface, overlay the area with 30mm of 6mm SMA surf 40/60 pen 60 PSV 
> > to levels and cross-falls.
> Same results with ACC2010. No truncation of the memo field.
> 
> Maybe there are some problems with the underlying query you use. What is 
> the sql statement of that query?
> 
> Ulrich

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web