Re: Truncated memo fields from queries within queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

Okay, I was looking for an explanation for the differences in what we are
seeing.

I opened Northwind in A2002 SP3 (exactly the same version number), and ran:
SELECT Employees.EmployeeID,
Employees.LastName,
Employees.Notes,
Len([Notes]) AS MemoLen
FROM Employees;

It clearly returned memo Notes beyond 255 characters.

The only difference I can see is that I am using msjet40.dll version
4.0.8618.0.
You might try downloading SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

--
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.

"Richard Reinertson" <RichardReinertson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:7E3812FC-E7B4-4F62-8FB5-6C4C0417110A@xxxxxxxxxxxxxxxx
> You're up a bit late, aren't you, Allen? I appreciate your help on this.
>
> Interesting that you can't replicate the problem. I tried it again this
> morning just to make sure I wasn't hallucinating, and it did the same
> thing.
> There is no DISTINCT in the SELECT statement--it's exactly as I wrote it
> for
> you in my previous note.
>
> The Access version is 2002 (10.6501.6735) SP3.
>
> The Jet version is 4.0.6508.0.
>
> Is it possible for my company's implementation of Access or Jet to specify
> memo field truncation in queries?
>
> By the way, because of the fact that I need to get this report out to the
> users ASAP, I went ahead this morning and created a higher-level query for
> my
> report, adding the memo fields through joins, and the report now gets the
> entire memo fields. However I'm still interested in a better solution to
> the
> problem--the resulting query structure is positively unholy, and will
> reduce
> speed and efficiency.
>
> Thanks for burning the midnight oil for me, Allen. Cheers, mate.
>
>
>
> "Allen Browne" wrote:
>
>> Hi Richard
>>
>> I am not able to replicate the problem you describe with the query you
>> gave.
>> I even tried adding the brackets just for fun, but the memo field was
>> returned in full.
>>
>> Any chance the query where you saw this had a DISTINCT predicate?
>>
>> If not, and it is reproducable, what version of Access?
>> And what version of msjet40.dll? (It's typically in windows\system32, and
>> its Properties has a Version tab.)
>>
>> I would rely on JET to return the full memo field unless you ask for some
>> kind of aggregation on it.
>>
>> "Richard Reinertson" <RichardReinertson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
>> in
>> message
>> news:03D2A79E-6793-4D57-B808-8FA91BE43750@xxxxxxxxxxxxxxxx
>> > Allen, thank you for your response.
>> >
>> > The funny thing is that I experimented with a simple query without
>> > aggregation of any sort and got the same result. If I specify
>> > something
>> > like
>> > this:
>> >
>> > SELECT (textfieldname), (textfieldname), (memofieldname)
>> > FROM (tablename);
>> >
>> > the resulting data*** truncates the memo field data to 255
>> > characters.
>> > If, however, I use this query as the data source for a report, the memo
>> > fields are NOT truncated on the report! If you happen to have a few
>> > minutes
>> > to try this yourself, I would be interested in your results.
>> >
>> > In the final analysis, I may need to construct the highest-level query
>> > with
>> > joins to get the memo fields, but I was hoping to avoid this solution
>> > if
>> > possible. Aside from the hassle, it seems inelegant.
>> >
>> > If you happen to have any other insights or revelations, I would very
>> > much
>> > appreciate your help. Thanks again.
>> >
>> >
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> If you ask it to perform any aggregation on a memo field, Access
>> >> truncates
>> >> it to the first 255 characters.
>> >>
>> >> If you have a Totals query, try choosing First instead of Group By
>> >> under
>> >> the
>> >> memo field. Instead of aggregating the values based on the memo field,
>> >> Access is then free to grab the first matching value and return the
>> >> entire
>> >> thing. You will then need to change the field name in your report from
>> >> (say)
>> >> Memo1 to FirstOfMemo1.
>> >>
>> >> If there is no GROUP BY but the query contains DISTINCT, you will also
>> >> have
>> >> the problem.
>> >>
>> >> The other common cause is anything in the Format property of the text
>> >> box
>> >> on
>> >> the report, or in the Format property of the field in the table.
>> >>
>> >> "Richard Reinertson" <RichardReinertson@xxxxxxxxxxxxxxxxxxxxxxxxx>
>> >> wrote
>> >> in
>> >> message
>> >> news:0E2D907B-A086-4CA0-81B5-96758BBFE59B@xxxxxxxxxxxxxxxx
>> >> >I needed to create a query to serve as the data source for a report.
>> >> >The
>> >> > data needs to be compiled from a LOT of tables, so I figured I would
>> >> > simplify
>> >> > my life by getting the data in pieces--I created one query to join
>> >> > four
>> >> > tables, another query to join three other tables, etc. Then I use
>> >> > the
>> >> > low-level queries as data sources for higher-level queries, until
>> >> > finally
>> >> > I
>> >> > bring all the data together in a single top-level query, which
>> >> > provides
>> >> > the
>> >> > data for the report.
>> >> >
>> >> > This all worked out great except for one thing: My memo fields are
>> >> > getting
>> >> > truncated to 255 characters. This doesn't happen when I use a
>> >> > single
>> >> > query
>> >> > to get the data for a report; only when I "stack" the queries.
>> >> >
>> >> > Does anybody know how to solve this problem without trying to put
>> >> > all
>> >> > the
>> >> > queries together into one massive query-from-hell?


.

Quantcast