Memo field only returns 255 characters

[Guest]

I'm looping through a recordset in VBA to read several fields, of which one
is a memo field. I was surprised that I only get 255 charcters returned even
when the field contains much more. This looks like a string variable,
although I'm not using that. Is there a limitation somewhere?

 

[John Nurick]

Hi Nova,

Does the memo field have a format property applied? IIRC there's a
gotcha with these.

 

[Guest]

well, the unicode compression is set to no, is that it?
(no clue why since it's on by default)

 

[Guest]

Ok, I noticed something. I loop through a recordset based on a query with
grouped fields. If I loop through the table directly or through a query
without grouping, then I get the whole field content.
So I guess my question is now: is there a property to set on the query to
get the whiole field content? As far as I can see, there's no such setting in
the query itself.

 

[John Nurick]

Are you grouping on the memo field, or doing anything else to it? I
can't find a reference to this as a problem, but I have a feeling at the
back of my mind.... There are circumstances in which performing an
operation on a memo field causes it to be interpreted as a text field,
and therefore truncated at 255 characters.

Can you post the SQL view of the query? That may give me an idea?

 

[John Nurick]

No, I meant something in its Format or Input Mask properties (in table
design view).

 

[Guest]

ok, here it is. In the meantime, I adjusted the sql (without grouping) to
have the full memo field, but it would be nice to know why the grouping
doesn't work. By the way: I create this sql in vba, so it's not a physical
query although it is based on one (which I also added below). Thanks !

'strCountSQL = SELECT qryBatches.BatchNumber, qryBatches.SendingID,
qryBatches.Remark, Sum(qryBatches.NumberOfTransactions) AS
SumOfNumberOfTransactions, qryBatches.SapBatch FROM qryBatches GROUP BY
qryBatches.BatchNumber, qryBatches.SendingID, qryBatches.Remark,
qryBatches.SapBatch HAVING ((qryBatches.SapBatch) = " & Chr(34) & strBatch &
Chr(34) & ") AND (qryBatches.ProjectID) = " & lngProjectID & ";"


this is the qryBatches:
SELECT tblBatches.*, tblStatusIbdxSap.StatusIbdxSap, tblProject.Project,
tblCompanies.CompanyCode, tblCompanies.LegalName, tblCompanies.CountryCode,
tblCompanies.IbdxLiveDate, tblCompanies.SRID, tblCompanies.OrganisationCode
FROM ((tblBatches LEFT JOIN tblProject ON tblBatches.ProjectID =
tblProject.ProjectID) LEFT JOIN tblCompanies ON tblBatches.SendingID =
tblCompanies.SendingID) LEFT JOIN tblStatusIbdxSap ON
tblBatches.StatusIbdxSapID = tblStatusIbdxSap.StatusIbdxSapID
ORDER BY tblBatches.BatchDate, tblBatches.SendingID, tblBatches.BatchNumber;

 

[Peter R. Fletcher]

Yes, this is a limitation of Queries, though, if you think about it
for a while, it is a reasonable limitation. If you do practically any
operation (Order By, Group, Select) which "looks at" the contents of a
memo field within a query, the contents of the field are implicitly
and silently truncated to 255 characters. The reason for this, I
believe, is to avoid the hopelessly long run times that would result
from having to go out to the "memo field contents storage area" within
the database multiple times for each record (as one would, if one was
sorting, selecting, or grouping on it).

The internal representation of a memo field item in the database can
be thought of as a 255 byte string in the main part of the record with
an additional pointer field showing the location and length of an
entry in a separate area of the database containing everything beyond
the first 255 bytes. The contents of the first 255 bytes can therefore
be (and are) handled as efficiently as those of a normal 255 byte
string field. Accessing the trailing characters is much less
efficient.

The standard workaround for this is to do your sorting, grouping, etc
in one Query which returns the primary keys of the records you want in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

Are you grouping on the memo field, or doing anything else to it? I
can't find a reference to this as a problem, but I have a feeling at the
back of my mind.... There are circumstances in which performing an
operation on a memo field causes it to be interpreted as a text field,
and therefore truncated at 255 characters.

Can you post the SQL view of the query? That may give me an idea?

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Peter R. Fletcher]

Yes, this is a limitation of Queries, though, if you think about it
for a while, it is a reasonable limitation. If you do practically any
operation (Order By, Group, Select) which "looks at" the contents of a
memo field within a query, the contents of the field are implicitly
and silently truncated to 255 characters. The reason for this, I
believe, is to avoid the hopelessly long run times that would result
from having to go out to the "memo field contents storage area" within
the database multiple times for each record (as one would, if one was
sorting, selecting, or grouping on it).

The internal representation of a memo field item in the database can
be thought of as a 255 byte string in the main part of the record with
an additional pointer field showing the location and length of an
entry in a separate area of the database containing everything beyond
the first 255 bytes. The contents of the first 255 bytes can therefore
be (and are) handled as efficiently as those of a normal 255 byte
string field. Accessing the trailing characters is much less
efficient.

The standard workaround for this is to do your sorting, grouping, etc
in one Query which returns the primary keys of the records you want in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

Are you grouping on the memo field, or doing anything else to it? I
can't find a reference to this as a problem, but I have a feeling at the
back of my mind.... There are circumstances in which performing an
operation on a memo field causes it to be interpreted as a text field,
and therefore truncated at 255 characters.

Can you post the SQL view of the query? That may give me an idea?

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[John Nurick]

The standard workaround for this is to do your sorting, grouping, etc
in one Query which returns the primary keys of the records you want in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

The only way I can improve on that is to add the missing . !

 

[jimmyshaw83]

John,

This was a previous post but it was buried very quickly.

I am sorry if this topic is already exhausted, but I am still aone in
the woods on this one. I have built a database to handle weekly and
monthly reports of what people are doing. They submit what they are
working on and then you input a date and the info is narrowed allowing
a report to be created containing all of the memo fields submitted
during those dates. My Source Query gets all the memo fields in their
entirety (greater than 255 chars), and then a Union Query puts the data
in a format that is easy to read i.e.:

Header A
User 1's Entry
User 3's Entry

Header B
User 1's Entry
User 2's Entry
User 4's Entry.....

The union query cuts off the fields at 255 characters. From previous
experience does this look like it is easy to solve. Here is some of
the SQL if that helps.

SELECT "a _GENERAL ITEMS" as Category, [General Items] as Item from
[Mk 36 01Jan Source] UNION
Select "c _OPN FUNDING", [Blank Space] FROM [Mk 36 01Jan Source]
UNION
Select "c (5) Procurement", [Procurement_OPN] FROM [Mk 36 01Jan
Source] UNION .....UNION
[Upcoming Events of Notice] FROM [Mk 36 01Jan Source];

Thank you very much for your time,

Jimmy Shaw
NSWC PHD Det Louisville Code G51
502-364-5276
(e-mail address removed)




Reply

 

[John Nurick]

A UNION query does this. AFAIK it's because part of the deal is that it
doesn't return duplicate records, and therefore must compare the
contents of all the fields being returned, including memo fields, which
get truncated for convenience. Try using UNION ALL to skip the
de-duplication.

John,

This was a previous post but it was buried very quickly.

I am sorry if this topic is already exhausted, but I am still aone in
the woods on this one. I have built a database to handle weekly and
monthly reports of what people are doing. They submit what they are
working on and then you input a date and the info is narrowed allowing
a report to be created containing all of the memo fields submitted
during those dates. My Source Query gets all the memo fields in their
entirety (greater than 255 chars), and then a Union Query puts the data
in a format that is easy to read i.e.:

Header A
User 1's Entry
User 3's Entry

Header B
User 1's Entry
User 2's Entry
User 4's Entry.....

The union query cuts off the fields at 255 characters. From previous
experience does this look like it is easy to solve. Here is some of
the SQL if that helps.

SELECT "a _GENERAL ITEMS" as Category, [General Items] as Item from
[Mk 36 01Jan Source] UNION
Select "c _OPN FUNDING", [Blank Space] FROM [Mk 36 01Jan Source]
UNION
Select "c (5) Procurement", [Procurement_OPN] FROM [Mk 36 01Jan
Source] UNION .....UNION
[Upcoming Events of Notice] FROM [Mk 36 01Jan Source];

Thank you very much for your time,

Jimmy Shaw
NSWC PHD Det Louisville Code G51
502-364-5276
(e-mail address removed)




Reply

The only way I can improve on that is to add the missing . !

 

[Peter R. Fletcher]

Unfortunately, at least on the basis of a quick test with dummy data,
UNION ALL does not solve the problem - the memo fields are still
truncated. I think that the OP is going to have to use Append Queries
to populate a temporary table by means of the approach set out in my
earlier message, and then report from the temporary table. I have a
suspicion that if we knew more about exactly what he was trying to do,
we might be able to come up with a more straightforward solution.

A UNION query does this. AFAIK it's because part of the deal is that it
doesn't return duplicate records, and therefore must compare the
contents of all the fields being returned, including memo fields, which
get truncated for convenience. Try using UNION ALL to skip the
de-duplication.

John,

This was a previous post but it was buried very quickly.

I am sorry if this topic is already exhausted, but I am still aone in
the woods on this one. I have built a database to handle weekly and
monthly reports of what people are doing. They submit what they are
working on and then you input a date and the info is narrowed allowing
a report to be created containing all of the memo fields submitted
during those dates. My Source Query gets all the memo fields in their
entirety (greater than 255 chars), and then a Union Query puts the data
in a format that is easy to read i.e.:

Header A
User 1's Entry
User 3's Entry

Header B
User 1's Entry
User 2's Entry
User 4's Entry.....

The union query cuts off the fields at 255 characters. From previous
experience does this look like it is easy to solve. Here is some of
the SQL if that helps.

SELECT "a _GENERAL ITEMS" as Category, [General Items] as Item from
[Mk 36 01Jan Source] UNION
Select "c _OPN FUNDING", [Blank Space] FROM [Mk 36 01Jan Source]
UNION
Select "c (5) Procurement", [Procurement_OPN] FROM [Mk 36 01Jan
Source] UNION .....UNION
[Upcoming Events of Notice] FROM [Mk 36 01Jan Source];

Thank you very much for your time,

Jimmy Shaw
NSWC PHD Det Louisville Code G51
502-364-5276
(e-mail address removed)




Reply

The standard workaround for this is to do your sorting, grouping, etc
in one Query which returns the primary keys of the records you want in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

The only way I can improve on that is to add the missing . !

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[John Nurick]

UNION ALL doesn't truncate my test data. [Sequence] is a memo field:

SELECT ID, TheTable, TheCount, Len(Sequence) AS Length
FROM (
SELECT ID, "Main" As TheTable, CCount AS TheCount, Sequence
FROM tblMain WHERE ID<10 AND ACount <75
UNION
SELECT ID, "Second" As TheTable, ACount AS TheCount, Sequence
FROM tblTwo WHERE ID<10 AND CCount <90
);

ID TheTable TheCount Length
1 Second 87 255
3 Main 82 255
3 Second 74 255
4 Second 112 255
5 Main 81 255
5 Second 73 255
7 Main 99 255
8 Main 91 255

SELECT ID, TheTable, TheCount, Len(Sequence) AS Length
FROM (
SELECT ID, "Main" As TheTable, CCount AS TheCount, Sequence
FROM tblMain WHERE ID<10 AND ACount <75
UNION ALL
SELECT ID, "Second" As TheTable, ACount AS TheCount, Sequence
FROM tblTwo WHERE ID<10 AND CCount <90
);

ID TheTable TheCount Length
3 Main 82 4959
5 Main 81 5151
7 Main 99 5439
8 Main 91 4991
1 Second 87 6015
3 Second 74 4959
4 Second 112 6591
5 Second 73 5151

What's up, doc?

Unfortunately, at least on the basis of a quick test with dummy data,
UNION ALL does not solve the problem - the memo fields are still
truncated. I think that the OP is going to have to use Append Queries
to populate a temporary table by means of the approach set out in my
earlier message, and then report from the temporary table. I have a
suspicion that if we knew more about exactly what he was trying to do,
we might be able to come up with a more straightforward solution.

A UNION query does this. AFAIK it's because part of the deal is that it
doesn't return duplicate records, and therefore must compare the
contents of all the fields being returned, including memo fields, which
get truncated for convenience. Try using UNION ALL to skip the
de-duplication.

John,

This was a previous post but it was buried very quickly.

I am sorry if this topic is already exhausted, but I am still aone in
the woods on this one. I have built a database to handle weekly and
monthly reports of what people are doing. They submit what they are
working on and then you input a date and the info is narrowed allowing
a report to be created containing all of the memo fields submitted
during those dates. My Source Query gets all the memo fields in their
entirety (greater than 255 chars), and then a Union Query puts the data
in a format that is easy to read i.e.:

Header A
User 1's Entry
User 3's Entry

Header B
User 1's Entry
User 2's Entry
User 4's Entry.....

The union query cuts off the fields at 255 characters. From previous
experience does this look like it is easy to solve. Here is some of
the SQL if that helps.

SELECT "a _GENERAL ITEMS" as Category, [General Items] as Item from
[Mk 36 01Jan Source] UNION
Select "c _OPN FUNDING", [Blank Space] FROM [Mk 36 01Jan Source]
UNION
Select "c (5) Procurement", [Procurement_OPN] FROM [Mk 36 01Jan
Source] UNION .....UNION
[Upcoming Events of Notice] FROM [Mk 36 01Jan Source];

Thank you very much for your time,

Jimmy Shaw
NSWC PHD Det Louisville Code G51
502-364-5276
(e-mail address removed)




Reply

John Nurick wrote:
The standard workaround for this is to do your sorting, grouping,
etc
in one Query which returns the primary keys of the records you want
in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

The only way I can improve on that is to add the missing . !

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Peter R. Fletcher]

Fascinating! I did pretty much what you did, but instead of
determining the length of the field, I ran the union query, put my
cursor in the memo field, and hit the <End> key, on the (not
unreasonable, I thought!) assumption that this would take me to the
end of the text in the field (which contained no <CR>s). I had put a
recognisable text sequence at the end of my long strings, and when I
didn't see it, I assumed that they had been truncated. It turns out
that <End> _doesn't_ take you to the end of the text under those
circumstances, and you are right - UNION ALL solves the problem.

UNION ALL doesn't truncate my test data. [Sequence] is a memo field:

SELECT ID, TheTable, TheCount, Len(Sequence) AS Length
FROM (
SELECT ID, "Main" As TheTable, CCount AS TheCount, Sequence
FROM tblMain WHERE ID<10 AND ACount <75
UNION
SELECT ID, "Second" As TheTable, ACount AS TheCount, Sequence
FROM tblTwo WHERE ID<10 AND CCount <90
);

ID TheTable TheCount Length
1 Second 87 255
3 Main 82 255
3 Second 74 255
4 Second 112 255
5 Main 81 255
5 Second 73 255
7 Main 99 255
8 Main 91 255

SELECT ID, TheTable, TheCount, Len(Sequence) AS Length
FROM (
SELECT ID, "Main" As TheTable, CCount AS TheCount, Sequence
FROM tblMain WHERE ID<10 AND ACount <75
UNION ALL
SELECT ID, "Second" As TheTable, ACount AS TheCount, Sequence
FROM tblTwo WHERE ID<10 AND CCount <90
);

ID TheTable TheCount Length
3 Main 82 4959
5 Main 81 5151
7 Main 99 5439
8 Main 91 4991
1 Second 87 6015
3 Second 74 4959
4 Second 112 6591
5 Second 73 5151

What's up, doc?

Unfortunately, at least on the basis of a quick test with dummy data,
UNION ALL does not solve the problem - the memo fields are still
truncated. I think that the OP is going to have to use Append Queries
to populate a temporary table by means of the approach set out in my
earlier message, and then report from the temporary table. I have a
suspicion that if we knew more about exactly what he was trying to do,
we might be able to come up with a more straightforward solution.

A UNION query does this. AFAIK it's because part of the deal is that it
doesn't return duplicate records, and therefore must compare the
contents of all the fields being returned, including memo fields, which
get truncated for convenience. Try using UNION ALL to skip the
de-duplication.

On 11 Feb 2005 16:13:17 -0800, (e-mail address removed) wrote:

John,

This was a previous post but it was buried very quickly.

I am sorry if this topic is already exhausted, but I am still aone in
the woods on this one. I have built a database to handle weekly and
monthly reports of what people are doing. They submit what they are
working on and then you input a date and the info is narrowed allowing
a report to be created containing all of the memo fields submitted
during those dates. My Source Query gets all the memo fields in their
entirety (greater than 255 chars), and then a Union Query puts the data
in a format that is easy to read i.e.:

Header A
User 1's Entry
User 3's Entry

Header B
User 1's Entry
User 2's Entry
User 4's Entry.....

The union query cuts off the fields at 255 characters. From previous
experience does this look like it is easy to solve. Here is some of
the SQL if that helps.

SELECT "a _GENERAL ITEMS" as Category, [General Items] as Item from
[Mk 36 01Jan Source] UNION
Select "c _OPN FUNDING", [Blank Space] FROM [Mk 36 01Jan Source]
UNION
Select "c (5) Procurement", [Procurement_OPN] FROM [Mk 36 01Jan
Source] UNION .....UNION
[Upcoming Events of Notice] FROM [Mk 36 01Jan Source];

Thank you very much for your time,

Jimmy Shaw
NSWC PHD Det Louisville Code G51
502-364-5276
(e-mail address removed)




Reply

John Nurick wrote:
The standard workaround for this is to do your sorting, grouping,
etc
in one Query which returns the primary keys of the records you want
in
the order, etc., that you want them, and to use this as a source for
the Query that returns all the columns you want, including the Memo
one

The only way I can improve on that is to add the missing . !

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher