Error 3071 in Report

[Ron Powell via AccessMonster.com]

I have an Access 97 database created by a user. She has a report with a
sorting and grouping clause using the prefix character of 1. She gets the
3071 error (This expression is typed incorrectly, or is too complex to be
evaluated). If I change the "prefix character" setting to "each value",
the report works fine. Unfortunately, she wants to group on that first
character. Any ideas?

 

[Mauricio Silva]

Insert a field in the query like: Left([FieldName],1) As PrefixChar
and use it as a grouping field

Note: Change FieldName for the name of your field

Mauricio Silva - 2005

 

[Ron Powell via AccessMonster.com]

I tried that and got the same error in the query. The query works by
itself but fails when you run the report with that grouping or by adding
the grouping in the query.

 

[Mauricio Silva]

I had this problem before. It happened because my queries were complicated
and the grouping on the report couldn't figure a way to work with... the only
way I figured to work around this, was to simplify my queries, and by this I
mean the ENTIRE chain of queries used.

For example, I changed my crosstab query for something like:

Query 1:
SELECT FiscalYear, DatePart("q", [ApprovalDate]), Amount
FROM [Charges]

Query 2:
Select FiscalYear, Sum(IIf(Quarter = 1, Amount, 0)) AS AmountQ1,
Sum(IIf(Quarter = 2, Amount, 0)) AS AmountQ2, Sum(IIf(Quarter = 3, Amount,
0)) AS AmountQ3, Sum(IIf(Quarter = 4, Amount, 0)) AS AmountQ4
FROM [Query 1]
GROUP BY FiscalYear

Ok, I have two queries now however, they are much simplier to Access to work
with. After this change, the error 3071 never showed up again.

I hope it helps.

Take care.

Mauricio Silva - 2005