By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Faulty characters after the 255 th character in longer than 255 string

PEB
Expert 100+
P: 1,418
Hi everybody,

I've a recordset based on a table.

Each field in the table is retreated in the recordset, so that the length of the field increases 5 times!

The retreatement is using this kind of expressions in the query:

Myf1:func([Field1])
..........
MyfN:func(FieldN)

As you can imagine there is fields that are longer than 100 symbols.. So the function returns more than 500 symbols!

In the obtained query there isn't any problem with the visualization of the respective fields - all characters are corectly displayed!

The problem is when this query is open from VB

Those fields are displayed correctly till the 255 th character and than only ??? ??? and characters like this! that seem to be get any part from the memory but nothing in common with the end of the field!

myr("[Myf1]") is limited to 255 characters proper function!

Other ways to get the data from the field?

Sure that there is, coz it's properly displayed in the query datasheet!

Help is greatly appreciated!
Nov 21 '06 #1
Share this Question
Share on Google+
12 Replies


missinglinq
Expert 2.5K+
P: 3,532
First off, what does retreated and retreatement mean? I've searched thru half a dozen programming dictionaries and found neither term. And what does this (I assume) function Myf1:func do? The involved field is obviously defined as a text field, which has a 255 character limit, which I expect explains the garbage after this point.
Nov 21 '06 #2

PEB
Expert 100+
P: 1,418
PEB
First off, what does retreated and retreatement mean? I've searched thru half a dozen programming dictionaries and found neither term. And what does this (I assume) function Myf1:func do? The involved field is obviously defined as a text field, which has a 255 character limit, which I expect explains the garbage after this point.
Retreatement - changing a string using function

However i don' think that u need the function that changes the string coz it works correctly

However no pbs to see it:

Expand|Select|Wrap|Line Numbers
  1. Function convert_from_charcode_file(Stri, File_name)
  2. Const l1 = 1
  3. Const l2 = 255
  4. Static letss(l1 To l2, 1 To 2)
  5. Dim i, p, rez
  6. i = 0
  7. p = 1
  8. rez = Stri
  9. Open File_name For Input As #100
  10. Do While Not EOF(100)
  11. i = i + 1
  12. Input #100, letss(p, i)
  13. 'Debug.Print letss(p, i)
  14.     If i = 2 Then
  15.         p = p + 1
  16.         i = 0
  17.     End If
  18. Loop
  19. Close #100
  20. For i = 1 To p
  21.     rez = Replace(ns(rez), letss(i, 1), letss(i, 2), 1, -1, vbBinaryCompare)
  22.     'replace_parameter(, , )
  23. Next i
  24. convert_from_charcode_file = rez
  25. End Function
  26.  
But i suggest you to see carefully this function:

Expand|Select|Wrap|Line Numbers
  1. Function create_file_from_SQL_SEP(SQL, File_name, sep) As Integer
  2. '?create_file_from_SQL_SEP("Joyau02", "D:\AJoyau.EXP", "; ")
  3. On Error Resume Next
  4. Dim line
  5. Dim mydb As Database
  6. Dim myr As Recordset
  7. Dim i
  8. Dim myupd_last, myupd_current
  9.  
  10. Set mydb = CurrentDb()
  11. Set myr = mydb.OpenRecordset(SQL)
  12.  
  13. Open File_name For Output As #1
  14. ' Close before reopening in another mode.
  15.  
  16. 'Stop
  17. myr.MoveLast
  18. myupd_last = myr.RecordCount
  19.     myr.MoveFirst
  20.     For myupd_current = 1 To myupd_last
  21.  
  22.         line = ""
  23.         For i = 0 To myr.fields.Count - 1
  24.             If i = 0 Then
  25.                 line = ns(myr(i))
  26.             Else
  27.                 line = line + sep + ns(myr(i)) 
  28.             End If
  29.         Next i
  30.         Print #1, line
  31.         myr.MoveNext
  32.     Next myupd_current
  33.  
  34. myr.Close
  35. Close #1
  36.  
  37. mydb.Close
  38. create_file_from_SQL_SEP = True
  39.  
  40. End Function
  41.  
  42.  
My question is why on the bolded line myr(i) don't give correctly all 600 or 700 caracthers in the respective field and gives only the first 255???
Nov 21 '06 #3

PEB
Expert 100+
P: 1,418
PEB
To have clear view

the query datasheet displays:


ГЕРГАНА,ЛОЗАНОВА,ЛАКПРОМ АД,,,ХИМИЧЕСКА,,,9963055,,,,,,,,СИНЧЕЦ 16,,,СОФИЯ,,,,,,,,,,,,,,,,,СРЕЩА НА 08;05;06.,ПРОДУКТИ НА ПЕПСИ КОЛА; СОКОВЕ ПРИСАН; ГАЗИ

that is good information

till myr(i)

gives correctly only 255 characters and after them caracthers like:

??? ???? ? ??,,,, ????? till the end of the respective string

For me not question of a string definition length!
Nov 21 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey vladi

I assume your table has this field set up as a Memo field. I'm not sure how these are treated in Recordsets.

Let me check it out.

Mary


To have clear view

the query datasheet displays:


ГЕРГАНА,ЛОЗАНОВА,ЛАКПРОМ АД,,,ХИМИЧЕСКА,,,9963055,,,,,,,,СИНЧЕЦ 16,,,СОФИЯ,,,,,,,,,,,,,,,,,СРЕЩА НА 08;05;06.,ПРОДУКТИ НА ПЕПСИ КОЛА; СОКОВЕ ПРИСАН; ГАЗИ

that is good information

till myr(i)

gives correctly only 255 characters and after them caracthers like:

??? ???? ? ??,,,, ????? till the end of the respective string

For me not question of a string definition length!
Nov 21 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey vladi

I assume your table has this field set up as a Memo field. I'm not sure how these are treated in Recordsets.

Let me check it out.

Mary
If you declare line as a String type does this make any difference. If I remember correctly not setting a type defaults to variant.
Nov 21 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Hi Mary,

Thanks for the suggestion!

For those fields they are all with size 250 characthers.
My table is named Current

Field1 - Size 250
..... - Size 250
Field65 -Size 250

After processing by this Function convert_from_charcode_file(Stri, File_name)

The length of the string become 5 times bigger

on query datasheet there is no problems in any field

But when the query is passed in the create_file_from_SQL_SEP(SQL, File_name, sep) As Integer

the field is returned correctly till the 255 characther. After that there is characters but not the real ones, fault characters!

Really no idea how to get all the field information!

Sure that there is a way, coz when i do export text it exports like a charm the query!

Thanks Mary

Hey vladi

I assume your table has this field set up as a Memo field. I'm not sure how these are treated in Recordsets.

Let me check it out.

Mary
Nov 21 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary,

Thanks for the suggestion!

For those fields they are all with size 250 characthers.
My table is named Current

Field1 - Size 250
..... - Size 250
Field65 -Size 250

After processing by this Function convert_from_charcode_file(Stri, File_name)

The length of the string become 5 times bigger

on query datasheet there is no problems in any field
Query is only for display. Where is the new string being stored.
Nov 21 '06 #8

missinglinq
Expert 2.5K+
P: 3,532
Unlike Mary, I wasn't assuming that the fields were set up as Memo fields, and
Field1 - Size 250
..... - Size 250
Field65 -Size 250
would seem to support that. Even if they were set up as Memo fields, there's a boatload of things you can't do with Memos in Queries and even types of Queries you can't include Memo fields in, with predictable results.

I have a reference file in my personal help directory, named, appropriately enough, Memo Field Angst, which lists just a few of the problems:

"Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

Also, if you have specified a format in the field's Format property, I believe
that will truncate the data that is displayed.

If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated."
Nov 21 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Query is only for display. Where is the new string being stored.
The problem isn't in line variable!

when i do

debug.print myr(i)

there is this problem too! So i don't think something like conversion problem between variables!

myr(i) gets the properties of the original field that is string (255) ?

But it is string 250 So it isn't so!

(HEADBANK)

Good evening :)
Nov 21 '06 #10

PEB
Expert 100+
P: 1,418
PEB
Query is only for display. Where is the new string being stored.
In fact the new string isn't stored...

As possible solution i'll try to store it in Memo field and than if it's ok i'll try to get it from there...

But this seems a bit wide solution.

The most interesting is that it returns the correct number of characters but not the correct characters after the 255 th!

I'll try to include this query in other query and than involve the second one in the function!

Also a try, doing copy from the query datasheet and paste in a variable into a module to see what happens!

And finnaly storing the results from the processing function in Memo field and than get the information from there.

Any other advises?
Nov 21 '06 #11

PEB
Expert 100+
P: 1,418
PEB
Unlike Mary, I wasn't assuming that the fields were set up as Memo fields, and

would seem to support that. Even if they were set up as Memo fields, there's a boatload of things you can't do with Memos in Queries and even types of Queries you can't include Memo fields in, with predictable results.

I have a reference file in my personal help directory, named, appropriately enough, Memo Field Angst, which lists just a few of the problems:

"Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

Also, if you have specified a format in the field's Format property, I believe
that will truncate the data that is displayed.

If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated."
No one of those satisfied!

No Memo,

No Grouping and Agregation

No Distinct Operator used

And finally no truncation coz it gives me the correct number of characters, but after the 255 th, the characters are wrong!

Maybe first i should save the information in Memo fields and than get it from there.... Sure that will work coz i've saved more than 7 tousand of numbers separated by commas there. They were the result of an other function! :) And when they were accessed from the memo field there wasn't problem!

Surprised that now there is such a problem... Maybe really there is some kind of relation with the basic field... If it is defined as text /String/ the result from this field and fields based on it can't pass to more than 255. If it arrives there is problems about... /in Visual Basic of course/

I'll try to retrieve a value by dlookup or simillar function to see what happens...

However if i save the values in Memo field it should be done using query, not programatically!
Nov 21 '06 #12

PEB
Expert 100+
P: 1,418
PEB
Yeah,

I've done the test! Transferring the values in Memo field using Insert query gives excelent results!

The problem isn't solved but i've escaped me from it! :)

Thank you both for your help and suggestions!
Nov 22 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.