This:

TRANSFORM First([_allText].Text) AS FirstOfText
SELECT [_allText].Expr1000
FROM _allText
GROUP BY [_allText].Expr1000
PIVOT [_allText].LetterSectionID;

hangs the application if _allText.Text is a memo field. But it's fine if
it's a text field.

Is it not possible to do what I'm trying to do?

Thanks, Emily

OK, answer to my own question, but it might help somebody else.

A crosstab is a (kinda) aggregate query, in this case using 'First' and you
can't use aggregate functions on memo fields.

But you can do this:

TRANSFORM First(Left([Text],512)) AS TextMemo
SELECT Expr1000
FROM _allText
GROUP BY Expr1000
PIVOT LetterSectionID;

Where I've used a function, Left(), to return a certain amount of text
(>255) and do the aggregate function on that. In fact that might be useful
for other sorts of things. Like trapping users who enter <64,000 characters
into a memo field but >the number I want them to.

So problem solved. At least as far as Access is concerned. This might be
going to SQL Server sometime. Do the same sort of restrictions apply to
nText and VarChar fields, does anybody know? We'll see.

Emily

Replying to myself again!!...

Turns out I was wrong. You can't use a memo field as the 'Transform' field
in a crosstab, but using Left(theMemoField, 10000) has the same effect.
Truncates it to a 255 character text field.

But only in Group By queries, as far as I can tell. A simple query that
wraps a memo field in left(memofield, 10000) doesn't truncate it.

I'm NOT Grouping on the memo field in this, its the actual value, as below.

All of which makes my way of doing this, well, useless. Looks like I'm going
to have to write a routine that creates the SQL Statement on the fly.

Emily

"Emily Jones" <emilyj@hotmail.comwrote in message
news:44dc5d3b$0$638$5a6aecb4@news.aaisp.net.uk...

OK, answer to my own question, but it might help somebody else.
>
A crosstab is a (kinda) aggregate query, in this case using 'First' and
you can't use aggregate functions on memo fields.
>
But you can do this:
>
TRANSFORM First(Left([Text],512)) AS TextMemo
SELECT Expr1000
FROM _allText
GROUP BY Expr1000
PIVOT LetterSectionID;
>
Where I've used a function, Left(), to return a certain amount of text
(>255) and do the aggregate function on that. In fact that might be useful
for other sorts of things. Like trapping users who enter <64,000
characters into a memo field but >the number I want them to.
>
So problem solved. At least as far as Access is concerned. This might be
going to SQL Server sometime. Do the same sort of restrictions apply to
nText and VarChar fields, does anybody know? We'll see.
>
Emily
>

Emily Jones wrote:

Replying to myself again!!...
>
Turns out I was wrong. You can't use a memo field as the 'Transform' field
in a crosstab, but using Left(theMemoField, 10000) has the same effect.
Truncates it to a 255 character text field.

Ages ago, when i was using Jet mostly, I seem to recall using
First(MemoFdName). I got the same effect, truncated to 255 characters.
This was for a form, and I got around it by having a oduhble click
event or pop up menu do a dlookup or something and give the whole field.
For a report where one would want the whole memo field and take
advantage of can grow/shrink properties , I can't quite remember what I
did, but I'm sure an on format/print event doing something similar to my
form procedure would do it...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me