Union All Query bombs report

[scott]

I have a union query that joins tables that have memo fields. I saw help in
the Discussion Group that suggested using Union All to get all the characters
in the query. It works fine for the query (I see all the characters in the
memo field in the datasheet), but it bombs the report. MS Access closes,
offering to repair and restart. Repairing and restarting does not work.

The report worked fine (aside from not having all the characters in the
field) when the query was Union instead of Union All (and it works now if I
change it back to just Union).

Any suggestions?

 

[scott]

On further searching, I found that joining on a non-indexed field can cause
problems. The other problem cited was grouping on the memo field, which my
report does not do. My report joins a calculated field on an indexed field.
If that is the problem, I don't know what to do. Is there a way to make a
calculated field an indexed field?

 

[Jeanette Cunningham]

Scott,
You could try this: replace the Union All query with a set of append
queries.
Set up a temp table just to use for reporting.
Append each query used in your union all query to the temp table, one at a
time.
You can often do the calculation field bit on the temp table, instead of in
the query that feeds the union all query.
Base the report on a query drawn from the temp table.

Jeanette Cunningham